Forum Discussion

James Gill's avatar
James Gill
Copper Contributor
Feb 01, 2018
Solved

Hide a set of columns

I'm using Excel for Mac version 16.9

 

My company provides me a huge dataset in the form of an .xlsx ... it has 152 columns all the way out to "EV".   I need maybe a couple dozen of these.   The data is re-published in the source format every few weeks and I take about ten sheets with a handful of rows and each with the same set of 152 columns.

 

So every couple of weeks I need to touch ten sheets "hide columns" until I see only these columns.

C,H,N-R,AE-AG,AI,BQ-BR,CM-CO,DB,DE-DF,DN-DO,EB-ED,EG,EJ,ES-ST

Is there a way I can apply this set to

a) a new sheet when I open it

b) all or bunch of sheets in a workbook

c) a sheet I have open

 

Thanks,

--jg

excel
  • Haytham Amairah's avatar
    Haytham Amairah
    Feb 01, 2018

    Hi James,

     

    You have to use some lines of code to automate this task, you can record all these steps through the https://support.office.com/en-us/article/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b to create a macro of this task so that you can reuse this Macro over and over again!

     

    Anyway, I've created the following two codes for you so that you can save them in the VBA code module, and start using them:

     

    #1 (Applied to all worksheets in the active workbook)

    Sub ShowOnlySpecificColumns1()
'Written by Haytham Amairah
'Last Updated: 2/2/2018
'Applied to all worksheets in the active workbook.

   For Each Sheet In Sheets
    Cells.Select
    Selection.EntireColumn.Hidden = True
    Range("C:C, H:H, N:R, AE:AG, AI:AI, BQ:BR,CM:CO, DB:DB, DE:DF, DN:DO, EB:ED, EG:EG,EJ:EJ, ES:ST") _
    .EntireColumn.Hidden = False
   Next Sheet
    
   Range("C1").Select
    
End Sub

     

    #2 (Applied to the active worksheet) 

    Sub ShowOnlySpecificColumns2()
'Written by Haytham Amairah
'Last Updated: 2/2/2018
'Applied to the active worksheet.

    Cells.Select
    Selection.EntireColumn.Hidden = True
    Range("C:C, H:H, N:R, AE:AG, AI:AI, BQ:BR,CM:CO, DB:DB, DE:DF, DN:DO, EB:ED, EG:EG,EJ:EJ, ES:ST") _
    .EntireColumn.Hidden = False
    
   Range("C1").Select
    
End Sub

     

    But I recommend you to save them in the https://support.office.com/en-us/article/create-and-save-all-your-macros-in-a-single-workbook-66c97ab3-11c2-44db-b021-ae005a9bc790 so that you can use them in other workbooks and make them available every time you open Excel.

     

    NOTE: The solution above is applied to Excel for Windows, but I think it's also applied to Excel for Mac, please try it and provide me with any feedback.

5 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Feb 01, 2018

    Hi James,

     

    You have to use some lines of code to automate this task, you can record all these steps through the https://support.office.com/en-us/article/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b to create a macro of this task so that you can reuse this Macro over and over again!

     

    Anyway, I've created the following two codes for you so that you can save them in the VBA code module, and start using them:

     

    #1 (Applied to all worksheets in the active workbook)

    Sub ShowOnlySpecificColumns1()
'Written by Haytham Amairah
'Last Updated: 2/2/2018
'Applied to all worksheets in the active workbook.

   For Each Sheet In Sheets
    Cells.Select
    Selection.EntireColumn.Hidden = True
    Range("C:C, H:H, N:R, AE:AG, AI:AI, BQ:BR,CM:CO, DB:DB, DE:DF, DN:DO, EB:ED, EG:EG,EJ:EJ, ES:ST") _
    .EntireColumn.Hidden = False
   Next Sheet
    
   Range("C1").Select
    
End Sub

     

    #2 (Applied to the active worksheet) 

    Sub ShowOnlySpecificColumns2()
'Written by Haytham Amairah
'Last Updated: 2/2/2018
'Applied to the active worksheet.

    Cells.Select
    Selection.EntireColumn.Hidden = True
    Range("C:C, H:H, N:R, AE:AG, AI:AI, BQ:BR,CM:CO, DB:DB, DE:DF, DN:DO, EB:ED, EG:EG,EJ:EJ, ES:ST") _
    .EntireColumn.Hidden = False
    
   Range("C1").Select
    
End Sub

     

    But I recommend you to save them in the https://support.office.com/en-us/article/create-and-save-all-your-macros-in-a-single-workbook-66c97ab3-11c2-44db-b021-ae005a9bc790 so that you can use them in other workbooks and make them available every time you open Excel.

     

    NOTE: The solution above is applied to Excel for Windows, but I think it's also applied to Excel for Mac, please try it and provide me with any feedback.

    • James Gill's avatar
      James Gill
      Copper Contributor
      Feb 09, 2018

      Hello Haytham,

       

      It seems that the ShowOnlySpecificColumns1 which should apply to all sheets in a workbook only applies to the currently selected sheet.

       

      I see that you have "for each - next" logic in there, it just doesn't appear to work.  Any clue as to why?

       

      Also, what is the purpose of the line "Range("C1").Select" line in each of these?

       

      Thanks again!

      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor
        Feb 09, 2018

        James,

         

        Sorry about this, I've updated the code with the fix:

         

        Sub ShowOnlySpecificColumns1()
'Written by Haytham Amairah
'Last Updated: 2/9/2018
'Applied to all worksheets in the active workbook.

   For Each Sheet In Sheets
    Sheet.Activate
    Cells.Select
    Selection.EntireColumn.Hidden = True
    Range("C:C, H:H, N:R, AE:AG, AI:AI, BQ:BR,CM:CO, DB:DB, DE:DF, DN:DO, EB:ED, EG:EG,EJ:EJ, ES:ST") _
    .EntireColumn.Hidden = False
    Range("C1").Select
   Next Sheet
    
End Sub

         

        The goal of this line is to save the active cell from drowning or disappearing with the columns that will be hidden!

        Range("C1").Select

        It moves the active cell to the shore, which is the first visible cell of each worksheet!

        Try to delete it, and see what happens!