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

excel

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.

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

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!

James Gill
Copper Contributor
Feb 09, 2018
Ah! I see you added Sheet.Activate there in the loop.

This is not only very helpful for my current problem, I've learned a lot of things about writing macros. Very powerful stuff!

Forum Discussion

Hide a set of columns