Feb 01 2018 11:00 AM
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
Feb 01 2018 11:12 PM - edited Feb 02 2018 12:30 AM
SolutionHi 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.
Feb 09 2018 08:18 AM
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!
Feb 09 2018 08:52 AM
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!
Feb 09 2018 09:10 AM
Feb 01 2018 11:12 PM - edited Feb 02 2018 12:30 AM
SolutionHi 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.