How to loop through columns and hide them based on a specified range of each column?-CodePudding

I am working on a macro in Excel that will hide any columns that are empty. However, they aren't TOTALLY empty, because I want the headers to still be there. For example, the headers are on Row 3. So, for column A, I want column A to be hidden if there is not data in the range("A4:A" & rng), rng being the last row. I was able to successfully write code for just column A and technically I could write this code for each row in the spreadsheet, but it goes from column A to AU. That would be a lot of code. There has to be a way to loop through each column and hide the column based on their range of row 4 through the last row. Please let me know!

Here is my code for just column A that works correctly. It loops through each cell in column A and if they are all empty, cellsEmpty is True and the column is hidden. If any of the cells have data in them, cellsEmpty is False and we exit the For. How do I loop through each column and apply this code to each column?

Sub hideEmptyColumns()

rng = WorksheetFunction.CountA(Worksheets("Sheet1").Range("A1:A1000"))   2
' 2 because of the top 2 blank rows

Dim i As Range
Dim cellsEmpty As Boolean
cellsEmpty = True

Application.ScreenUpdating = False

For Each i In Range("A4:A" & rng)
    If i.Value <> "" Then
        cellsEmpty = False
        Exit For
    End If
Next

If cellsEmpty = True Then
    Columns("A").Hidden = True
End If

Application.ScreenUpdating = True

End Sub

CodePudding user response：

Loop through each column and get the last row, if less than or equal to 3 then hide.

    Dim lc As Long
    Dim lr As Long
    
    Dim i As Long
    
    With Sheets("Sheet1")
        lc = .Cells(3, .Columns.Count).End(xlToLeft).Column
        For i = 1 To lc
            lr = .Cells(.Rows.Count, i).End(xlUp).Row
            If lr <= 3 Then
                Columns(i).Hidden = True
            End If
        Next i
    End With