CODEWITHSUNDEEP
excelvbahiderows
justtrying
justtrying

Reputation: 5

If a cell is blank, hide next n rows, VBA

In a worksheet, "Scenarios," I'm trying to hide groups of entire rows based on whether or not the cell value in column B for that group of rows is contains the text "not included". For instance, in the range B19:B77, I have sections for accounts in each 5 rows. The first row in each section has either the account name or "not included." If it says "not included," I would like to hide that row and the subsequent 4 rows (e.g. rows 19 through 23). I am aware of how to hide entire rows based on the value of a cell (code below), but I'd like to figure out how to hide the additional rows. 

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
        For Each xRg In Range("B19:B77")
            If xRg.Value = "" Then
                xRg.EntireRow.Hidden = True
            Else
                xRg.EntireRow.Hidden = False
            End If
        Next xRg
    Application.ScreenUpdating = True
End Sub

Thank you for your help, in advance!

Upvotes: 0

Views: 1227

Answers (1)

YowE3K
YowE3K

Reputation: 23974

The For loop could look something like:

Dim r As Long
For r = 19 To 77 Step 5
    Rows(r & ":" r + 4).Hidden = Cells(r, "B").Value = "not included"
Next

Note: That 77 looks strange. If everything is in groups of 5 rows, your last "account name" will be in row 74, which means the last group seems to only be 4 rows (74 to 77).

Upvotes: 2

Related Questions