• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto Hide and Unhide Specify Column

C

cyliyu

Member
I have a workbook consists of 50 sheets.
Each time when I release it to the end users, I need to manually hide some of the column.
And after end users filled up and return me the workbook, I will need to unhide them before make change.
How can I automate it.
1) Title and Home-No action required
2) All the "4Column" spreadsheet will have the column "G:H" and "N:O" hidden.
3) All the "5Column" spreadsheet will have the column "G:H", "N:O" and "S" hidden.
and so on.

I can write the code for only one spreadsheet and continue column, but not the above and need help please.
 

Attachments

  • Book1.xlsx
    32.4 KB · Views: 8
Code: 
Sub HidEm()
For Each Sht In ActiveWorkbook.Sheets
  If UCase(Left(Sht.Name, 7)) = "4COLUMN" Then Sht.Range("G:H,N:O").EntireColumn.Hidden = True
  If UCase(Left(Sht.Name, 7)) = "5COLUMN" Then Sht.Range("G:H,N:O,S:S").EntireColumn.Hidden = True
Next Sht
End Sub

Sub ShowEm()
For Each Sht In ActiveWorkbook.Sheets
  If UCase(Left(Sht.Name, 7)) = "4COLUMN" Then Sht.Range("G:H,N:O").EntireColumn.Hidden = False
  If UCase(Left(Sht.Name, 7)) = "5COLUMN" Then Sht.Range("G:H,N:O,S:S").EntireColumn.Hidden = False
Next Sht
End Sub
 
Thanks P45cal.
Your code with some limitation to the sheet name.
I have over 50 sheets with different name and length.
Still trying to figure out how to shorten the code.
 
p45cal said:
What planet?
Click to expand...

what I meant was I may need to rename all my spread sheet to have the similar length of char in order to use the code
Code: 
If UCase(Left(Sht.Name, 7)) = "4COLUMN" Then Sht.Range("G:H,N:O").EntireColumn.Hidden = False

as currently, my spreadsheet named such as PCSE, AXKU, APU, MINIAUX, cPCE, stkPCE, LPCE etc etc....
 
cyliyu
Do You know which sheets are "4Column"- and which are "5Column"-sheets?
Do You have a list of those or
how to know ...which one is which?
 
Is there some characteristic on each sheet which would allow you to determine whether you want the S column hidden or not? A header perhaps, but anything would do.
 
I have simplify & rename my worksheet as below:-

1) Column to hide:- H:I, N:O, U:X
Spreadsheet Name:- PCA/ACA/EVR/RIOM32/RIOM38/PECU/EDCU/AUXMINI/MIRECTIR/VDU/
cPCA/cACA/cEVR/cRIOM32/cRIOM38/cPECU/cAUXMINI/cMIRECTIR/cVDU/
sPCA/sACA/sEVR/sRIOM32/sRIOM38/sPECU/sAUXIMINI/sMIRECTIR/sVDU/
lPCA/lACA/lEVR/lRIOM32/lRIOM38/lPECU/lAUXIMINI/lMIRECTIR/lVDU

2) Column to hide:- I:J, O: P, V:Y
Spreadsheet Name:-
AXU/XPU/BCE/
cAXU/cXPU/xBCE/
sAXU/sXPU/sBCE

3) Column to hide:- H:I, N:O, U:U
Spreadsheet Name:-
CPUCONTR/INVCONTR/ENCODER/
cCPUCONTR/cINVCONTR/cENCODER/
sCPUCONTR/sINVCONTR/sENCODER
 
Completely untested:
Code: 
Sub HideEm()
type1 = "/PCA/ACA/EVR/RIOM32/RIOM38/PECU/EDCU/AUXMINI/MIRECTIR/VDU/cPCA/cACA/cEVR/cRIOM32/cRIOM38/cPECU/cAUXMINI/cMIRECTIR/cVDU/sPCA/sACA/sEVR/sRIOM32/sRIOM38/sPECU/sAUXIMINI/sMIRECTIR/sVDU/lPCA/lACA/lEVR/lRIOM32/lRIOM38/lPECU/lAUXIMINI/lMIRECTIR/lVDU/"  'H:I,N:O,U:X
type2 = "/AXU/XPU/BCE/cAXU/cXPU/xBCE/sAXU/sXPU/sBCE/"  ' I:J,O:P,V:Y
type3 = "/CPUCONTR/INVCONTR/ENCODER/cCPUCONTR/cINVCONTR/cENCODER/sCPUCONTR/sINVCONTR/sENCODER/"  'H:I,N:O,U:U
For Each sht In ActiveWorkbook.Sheets
  searchterm = "/" & sht.Name & "/"
  If InStr(1, type1, searchterm, vbTextCompare) > 0 Then sht.Range("H:I,N:O,U:X").EntireColumn.Hidden = True
  If InStr(1, type2, searchterm, vbTextCompare) > 0 Then sht.Range("I:J,O:P,V:Y").EntireColumn.Hidden = True
  If InStr(1, type3, searchterm, vbTextCompare) > 0 Then sht.Range("H:I,N:O,U:U").EntireColumn.Hidden = True
Next sht
End Sub
Sub ShowEm()
type1 = "/PCA/ACA/EVR/RIOM32/RIOM38/PECU/EDCU/AUXMINI/MIRECTIR/VDU/cPCA/cACA/cEVR/cRIOM32/cRIOM38/cPECU/cAUXMINI/cMIRECTIR/cVDU/sPCA/sACA/sEVR/sRIOM32/sRIOM38/sPECU/sAUXIMINI/sMIRECTIR/sVDU/lPCA/lACA/lEVR/lRIOM32/lRIOM38/lPECU/lAUXIMINI/lMIRECTIR/lVDU/"  'H:I,N:O,U:X
type2 = "/AXU/XPU/BCE/cAXU/cXPU/xBCE/sAXU/sXPU/sBCE/"  ' I:J,O:P,V:Y
type3 = "/CPUCONTR/INVCONTR/ENCODER/cCPUCONTR/cINVCONTR/cENCODER/sCPUCONTR/sINVCONTR/sENCODER/"  'H:I,N:O,U:U
For Each sht In ActiveWorkbook.Sheets
  searchterm = "/" & sht.Name & "/"
  If InStr(1, type1, searchterm, vbTextCompare) > 0 Then sht.Range("H:I,N:O,U:X").EntireColumn.Hidden = False
  If InStr(1, type2, searchterm, vbTextCompare) > 0 Then sht.Range("I:J,O:P,V:Y").EntireColumn.Hidden = False
  If InStr(1, type3, searchterm, vbTextCompare) > 0 Then sht.Range("H:I,N:O,U:U").EntireColumn.Hidden = False
  'Alternative 3 lines to the 3 above if ALL columns need to be showing:
  'If InStr(1, type1, searchterm, vbTextCompare) > 0 Then sht.Columns.Hidden = False
  'If InStr(1, type2, searchterm, vbTextCompare) > 0 Then sht.Columns.Hidden = False
  'If InStr(1, type3, searchterm, vbTextCompare) > 0 Then sht.Columns.Hidden = False
Next sht
End Sub

Sub ShowEm2() 'an alternative if you want ALL columns in each of the named sheets to be showing.
typeAll = "/PCA/ACA/EVR/RIOM32/RIOM38/PECU/EDCU/AUXMINI/MIRECTIR/VDU/cPCA/cACA/cEVR/cRIOM32/cRIOM38/cPECU/cAUXMINI/cMIRECTIR/cVDU/sPCA/sACA/sEVR/sRIOM32/sRIOM38/sPECU/sAUXIMINI/sMIRECTIR/sVDU/lPCA/lACA/lEVR/lRIOM32/lRIOM38/lPECU/lAUXIMINI/lMIRECTIR/lVDU/AXU/XPU/BCE/cAXU/cXPU/xBCE/sAXU/sXPU/sBCE/CPUCONTR/INVCONTR/ENCODER/cCPUCONTR/cINVCONTR/cENCODER/sCPUCONTR/sINVCONTR/sENCODER/"
For Each sht In ActiveWorkbook.Sheets
  If InStr(1, typeAll, "/" & sht.Name & "/", vbTextCompare) > 0 Then sht.Columns.Hidden = False
Next sht
End Sub
 
cyliyu
... or could You do it this way?
Sample file has those buttons, but not all sheets!

Code: 
Sub Hide_Columns()
    Do_It "Hide", True
End Sub

Sub Show_Columns()
    Do_It "Show", False
End Sub

Sub Do_It(msg, DI)
    Application.ScreenUpdating = False
    Dim strS(2)
    strS(0) = Split("PCA/ACA/EVR/RIOM32/RIOM38/PECU/EDCU/AUXMINI/MIRECTIR/VDU", "/")
    strS(1) = Split("AXU/XPU/BCE", "/")
    strS(2) = Split("CPUCONTR/INVCONTR/ENCODER", "/")
    For tbls = 1 To Sheets.Count
        tbl_name = Sheets(tbls).Name
        For s = 0 To 2
            For i = LBound(strS(s)) To UBound(strS(s))
                If InStrRev(tbl_name, strS(s)(i)) > 0 Then
                    If s = 0 Then Sheets(tbls).Range("H:I, N:O, U:X").EntireColumn.Hidden = DI
                    If s = 1 Then Sheets(tbls).Range("I:J, O:P, V:Y").EntireColumn.Hidden = DI
                    If s = 2 Then Sheets(tbls).Range("H:I, N:O, U:U").EntireColumn.Hidden = DI
                    i = 9999:    s = 9
                End If
            Next i
        Next s
    Next tbls
    Application.ScreenUpdating = True
    MsgBox msg & " Done"
End Sub
 

Attachments

  • Book1.xlsb
    34.4 KB · Views: 8
Thanks Vletm.
Your code was stored in the HOME sheet.
Do I need to transfer to my worksheet under the Modules?
 
cyliyu
Those are in 'HOME' because, for me it is a logic place, there are those buttons.
But You can copy those ... anywhere.
 
Thank vletm.

May I have a request to incorperate the code to hide/unhide the headings including those protected or hiden column in a sheets?

I tried the following code but it will not work for protected sheets or sheet with hiden coloumn.

Code: 
Sub hideheadings()
     Dim wsSheet As Worksheet
     Application.ScreenUpdating = False
          For Each wsSheet In ThisWorkbook.Worksheets
          If Not wsSheet.Name = "Blank" Then
          wsSheet.Activate
          With ActiveWindow
         .DisplayHeadings = False
         '.DisplayWorkbookTabs = True
         .DisplayHorizontalScrollBar = False
  End With
  End If
  Next wsSheet
  Application.ScreenUpdating = True
End Sub
 
Last edited:
cyliyu ... hmm?
... ActiveWindow
... it would effect to all sheets!
I would do it this way:
1) Check 'ThisWorkbook's code
2) and for original case,
I would do it as written in 'Do_It_With_Yellow'
... then no matter of sheets names (mark with yellow as in file)
 

Attachments

  • Book1.xlsb
    38.7 KB · Views: 11
Thanks vletm,

I may not explaining clear enough.
What I want was to hide/unhide the Column (A,B....) and Row (1,2.....).

upload_2017-12-3_20-7-44.png
 
cyliyu .. You did ...
but how did You 'test' that file?
a) if You would like to hide those from 'HOME'-sheet then edit like below!
b) if You would like to use same 'Sub' with other file then edit 'sheet names' as needed (ex if there is "Blank"-named sheet )
c) if both cases that [un]hiding would happen AFTER activate sheet (not before)

Note: ThisWorkbook-code, below, has modified! This also [un]hides 'Gridlines'!
Code: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Select Case Sh.Name
        Case "Title", "HOME"
            DI = False
        Case Else
            DI = True
    End Select
    With ActiveWindow
        .DisplayGridlines = DI
        .DisplayHeadings = DI
        .DisplayHorizontalScrollBar = DI
    End With
End Sub
 
If I change the code and added as a macro as follow, I have a compile error.
any advise?
Code: 
Sub Hide_Headings()
  Dim sh As Worksheet
  Select Case sh.Name
  Case "Title", "HOME"
  DI = True
  Case Else
  DI = False
  End Select
  With ActiveWindow
  .DisplayHeadings = DI
  .DisplayHorizontalScrollBar = DI
  End With

End Sub
 
cyliyu
1) Use my code ... or ...
2) What would be value of sh? ... hmm ... nothing! if nothing then ...
3) Of course it's possible to add On Error Resume Next
before Dim .. but ... then no need those lines before With ActiveWindow!
4) as written #15, ActiveWindow will effect to all sheets.
5) ... advice ... hmm? ... what should that macro do?
 
vletm,
Yes, I am using your code. I wanted to have the code in the Module such as sub Hide_Headings () and sub Unhide_Headings () instead of ThisWorkBook

All the spreadsheets headings will be hide except "Title" and "Home".

I want to try using Select Case statement.
 
cyliyu
That Your Hide_Headings
... works with both way ... hide and unhide ... then no need both!
If You really would like write more code
Then You just have to run Your 'Hide_Headings' every time then change sheet!
... You have about 50 sheets ...
then You gotta have ~50 times same code which would run Your Hide_Headings
... instead of ONE Marco in ThisWorkBook! Okay!
change
Sub Hide_Headings()
Dim sh As Worksheet
Select Case sh.Name
to
Sub Hide_Headings()
Select Case Activesheet.Name
 
You must log in or register to reply here.
Back
Top