Hide a set of columns

[James Gill]

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

[#2 · Accepted solution]

Hi James,

You have to use some lines of code to automate this task, you can record all these steps through the Macro Recorder 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 Personal Macro Workbook 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.

[#3]

Thank you! This is brilliant.

[#4]

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!

[#5]

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!

[#6]

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!