CODEWITHSUNDEEP
vbaexcel
SteelyDanFan
SteelyDanFan

Reputation: 79

Hide the whole row when a range of cells is empty

I have a range of cells B2:AB40.

If every cell in each row within the the range is blank (by which I mean no text or numbers, just colour fill and border formatting), I want to hide the whole of the row using a macro.

e.g.

If every cell in the range B2:AB2 is blank then hide all of row 2.

If every cell in the range B3:AB3 is blank then hide all of row 3

If every cell in the range B4:AB4 is blank then hide all of row 4..etc etc etc

Up to and including row 40.

N.B. Each cell in column A and AC in every row adjacent to the specified range will always have text (someone's name and a formula result respectively) and this cannot be changed.

I have seen various ways of doing this based on a single cell but cannot seem to adapt them for my purposes.

Any help is appreciated.

Upvotes: 0

Views: 3565

Answers (2)

Tommeck37
Tommeck37

Reputation: 131

Try this

Sub HideRangeIfEmpty()

  
    
    If Application.WorksheetFunction.CountA(Range("b2:AB2")) = 0 Then
        Range("b2:AB2").EntireRow.Hidden = True

    End If
    
    
End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Sub RowHider()
    Dim I As Long, wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    For I = 2 To 40
        If wf.CountA(Range("B" & I & ":AB" & I)) = 0 Then
            Rows(I).Hidden = True
        Else
            Rows(I).Hidden = False
        End If
    Next I
End Sub

Note the usage of a worksheet function in VBA.

Upvotes: 3

Related Questions