0

I found the below code in this thread (Excel - any way to auto hide empty columns when filtering rows?) which hides columns with only a header in them but no data. It works fine for what I'm doing, but it only does one worksheet, whereas I need it to do the whole workbook.

Hopefully that's an easy change, but I'm new to this - would someone be kind enough to amend the below, please?

Sub KolumnHider()
Dim wf As WorksheetFunction
Dim i As Long, r As Range

Set wf = Application.WorksheetFunction
For i = 1 To 1000
    Set r = Cells(1, i).EntireColumn
    If wf.CountA(r) < 2 Then r.Hidden = True
Next i
End Sub

Thanks, Ewan

0

Try something along the lines of

Dim wf As WorksheetFunction
Dim i As Long, r As Range
Dim sh as Worksheet

Set wf = Application.WorksheetFunction
For Each sh in ActiveWorkbook.Worksheets
    For i = 1 To 1000
        Set r = sh.Cells(1, i).EntireColumn
        If wf.CountA(r) < 2 Then r.Hidden = True
    Next i
Next

Additionally, I would replace the "1000" in that code by

 sh.Cells.SpecialCells(xlCellTypeLastCell).Column

to make the code loop over the exact number of columns required, not just 1000, which may be too many or too few.

  • Thanks very much - that's great. Was going to replace the 1000 with a lower number, but that code will make it much neater. Much appreciated. – Ewan Aug 9 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.