AutoHide a Row If All Cells in Range are blank

[CivilENGR]

Trying to figure out how to write code such that If ALL the cells in a range, say (B4:Z4), are blank then hide the row. I need this to be applied to multiple ranges however, not just that specific range.

Idea is something like:

Sub HideRows()
For k = 4 To 40
IF All cells in Range ("B(k):Z(k)") = ""
Then
EntireRow.Hidden = True
Else
EntireRow.Hidden = False
Next k
End Sub

Fairly new to coding so not sure if I'm close or not... Any help is greatly appreciated!

 

[Joe4]

Welcome to the Board!

Try this:

Sub HideRows()

    Dim k As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False
    
    For k = 4 To 40
        Set rng = Range("B" & k & ":Z" & k)
        If Application.WorksheetFunction.CountBlank(rng) = 25 Then
            Rows(k).Hidden = True
        Else
            Rows(k).Hidden = False
        End If
    Next k
    
    Application.ScreenUpdating = True
        
End Sub

 

[CivilENGR]

Thanks! I think I'm almost there. I forgot to mention I'm using a command button to initiate this code (not sure if it makes a difference). I input the code but nothing happens when I click the command button (no errors either).

My code is:

Private Sub HideRows_Click()
    Dim i As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False
    For i = 4 To 40
     Set rng = Range("B" & k & ":Z" & k)
        If Application.WorksheetFunction.CountBlank(rng) = 25 Then
        Rows(i).Hidden = True
      Else
        Rows(i).Hidden = False
     End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[CivilENGR]

Just kidding! I realized I changed the index variable. It Works! Thank you much!

 

[Joe4]

You are welcome.