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

Question

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!

YowE3K · Accepted Answer · 2017-11-07 00:40:20Z

2

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).

answered Nov 7, 2017 at 0:40

YowE3K

24k7 gold badges27 silver badges45 bronze badges

Brilliant! Thank you!! I only modified slightly:
– justtrying
Commented Nov 7, 2017 at 0:47
Private Sub Worksheet_Activate() Dim r As Long Application.ScreenUpdating = False For r = 19 To 73 Step 5 Rows(r & ":" & r + 4).Hidden = Cells(r, "B").Value = "not included" Next Application.ScreenUpdating = True End Sub
– justtrying
Commented Nov 7, 2017 at 0:48
I'd prefer `Rows(r).Resize(5).Hidden = Cells(r, "B").Value = "not included" but this works +1
– user6432984
Commented Nov 7, 2017 at 0:49
@ThomasInzina I thought of doing Resize on Rows, but wasn't sure it would work, and I knew specifying the row numbers as a string would work, so I went the path of least resistance (or, rather, least effort) and used the method that wouldn't require me testing anything :D
– YowE3K
Commented Nov 7, 2017 at 0:50
1

Rows returns a collection of all the cells in the rows. As soon as you Resize a Row using the ColumnSize parameter you target the first cell in the collection. Your example returns A10:L13. Of course Rows(10).Resize(4,12 ).EntireRow.Address will extend the selection to return all the cells in rows targeted -> 10:13
– user6432984
Commented Nov 7, 2017 at 0:57

| Show 1 more comment

Collectives™ on Stack Overflow

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

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related