CODEWITHSUNDEEP
excelvba
Rob Bednark
Rob Bednark

Reputation: 28152

Excel macro: how to ignore header row when checking columns

This HideEmptyColumns() macro hides all columns that don't have any data. How do I modify it so that it ignores the header row (row 1)?

Sub HideEmptyColumns()
  Dim Col As Range
  For Each Col In ActiveSheet.UsedRange.Columns
     Col.EntireColumn.Hidden = RangeIsEmpty(Col)
  Next Col
End Sub

Function RangeIsEmpty(R As Range) As Boolean
  Dim Cell As Range
  RangeIsEmpty = True
  For Each Cell In R.Cells
     If Cell.Value <> "" Then
        RangeIsEmpty = False
        Exit For
     End If
  Next Cell
End Function

Upvotes: 0

Views: 6299

Answers (1)

Sorceri
Sorceri

Reputation: 8033

Just check to see what row its on

Sub HideEmptyColumns()
  Dim Col As Range
  For Each Col In ActiveSheet.UsedRange.Columns
     Col.EntireColumn.Hidden = RangeIsEmpty(Col)
  Next Col
End Sub

Function RangeIsEmpty(R As Range) As Boolean
  Dim Cell As Range
  RangeIsEmpty = True
  For Each Cell In R.Cells
     'are we not on the first row (header row)
     If Cell.Row <> 1 Then
        If Cell.Value <> "" Then
           RangeIsEmpty = False
           Exit For
        End If
     End If
  Next Cell
End Function

Upvotes: 3

Related Questions