Built in tool or VBA to hide all unused columns with a catch

[xdriver]

I have a spreadsheet that is exported from one of my databases that has nearly 900 columns with headers, however not all columns have data in them other than the header. Is there a built in toggle to hide the columns without data in them? I would obviously want to hide the header row if there is nothing else in the column, as well as a way to unhide all columns at once if possible. Thank you.

Excel for Mac 365 16.8.2

 

[rpaulson]

try this

Sub hide_blanks()

' this will hide the column if its blank or if there is only 1 cell with data (The header)
Application.ScreenUpdating = False
Application.EnableEvents = False

On Error Resume Next

For c = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
   If Columns(c).Cells.SpecialCells(xlCellTypeConstants).Count = 1 Then Columns(c).Hidden = True
Next c

On Error GoTo 0
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Sub Show_All_columns()
Columns.EntireColumn.Hidden = False

End Sub

 

[xdriver]

try this

Sub hide_blanks()

' this will hide the column if its blank or if there is only 1 cell with data (The header)
Application.ScreenUpdating = False
Application.EnableEvents = False

On Error Resume Next

For c = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
   If Columns(c).Cells.SpecialCells(xlCellTypeConstants).Count = 1 Then Columns(c).Hidden = True
Next c

On Error GoTo 0
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Sub Show_All_columns()
Columns.EntireColumn.Hidden = False

End Sub

That appears to have worked perfectly. Thank you so much. I figured it had to be something with "count." I should really start trying to learn some VBA for myself. Again, thank you very much.

 

[xdriver]

Actually, how would I modify that for a range of count values? If I decided that I wanted to hide a column with 1-5 total values.

 

[rpaulson]

If Columns(c).Cells.SpecialCells(xlCellTypeConstants).Count =<5 Then Columns(c).Hidden = True

 

[xdriver]

Many thanks