1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by cyliyu, Nov 30, 2017.

  1. cyliyu

    cyliyu Member

    Messages:
    125
    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.

    Attached Files:

  2. p45cal

    p45cal Well-Known Member

    Messages:
    1,001
    Code (vb):
    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
     
    Thomas Kuriakose and cyliyu like this.
  3. cyliyu

    cyliyu Member

    Messages:
    125
    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.
  4. p45cal

    p45cal Well-Known Member

    Messages:
    1,001
    What planet?
  5. cyliyu

    cyliyu Member

    Messages:
    125
    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 (vb):
    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....
  6. vletm

    vletm Well-Known Member

    Messages:
    3,271
    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?
  7. p45cal

    p45cal Well-Known Member

    Messages:
    1,001
    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.
  8. cyliyu

    cyliyu Member

    Messages:
    125
    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
  9. p45cal

    p45cal Well-Known Member

    Messages:
    1,001
    Completely untested:
    Code (vb):
    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 likes this.
  10. cyliyu

    cyliyu Member

    Messages:
    125
    Thanks for the great help. It solved my problem.
  11. vletm

    vletm Well-Known Member

    Messages:
    3,271
    cyliyu
    ... or could You do it this way?
    Sample file has those buttons, but not all sheets!

    Code (vb):

    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
     

    Attached Files:

    cyliyu likes this.
  12. cyliyu

    cyliyu Member

    Messages:
    125
    Thanks Vletm.
    Your code was stored in the HOME sheet.
    Do I need to transfer to my worksheet under the Modules?
  13. vletm

    vletm Well-Known Member

    Messages:
    3,271
    cyliyu
    Those are in 'HOME' because, for me it is a logic place, there are those buttons.
    But You can copy those ... anywhere.
  14. cyliyu

    cyliyu Member

    Messages:
    125
    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 (vb):

    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: Dec 3, 2017
  15. vletm

    vletm Well-Known Member

    Messages:
    3,271
    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)

    Attached Files:

  16. cyliyu

    cyliyu Member

    Messages:
    125
    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
  17. vletm

    vletm Well-Known Member

    Messages:
    3,271
    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 (vb):

    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
     
    cyliyu likes this.
  18. cyliyu

    cyliyu Member

    Messages:
    125
    Thanks for your help.
    Yes, it did. I make a mistake in your code.
  19. cyliyu

    cyliyu Member

    Messages:
    125
    If I change the code and added as a macro as follow, I have a compile error.
    any advise?
    Code (vb):

    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
  20. vletm

    vletm Well-Known Member

    Messages:
    3,271
    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?
    sathishsusa likes this.
  21. cyliyu

    cyliyu Member

    Messages:
    125
    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.
  22. vletm

    vletm Well-Known Member

    Messages:
    3,271
    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
    cyliyu likes this.
  23. cyliyu

    cyliyu Member

    Messages:
    125
    Noted. I will leave the code in ThisWorkBook instead.
    Once again, thanks for your help.

Share This Page