Macro to hide all rows that ...

[thankyou]

Looking for a VBA module that will hide all rows except adjacent rows that have "9" as their value in one cell of column B, and "5" in the cell below it, like this:

Column B

1
9
5
18
23
3
11
5
9
4
9
5
22

So, in the above example, you'd hide all rows except the ones I've bolded for you here, namely rows 2,3,11 & 12, leaving:

Column B

9
5
9
5
etc


That will allow me to do analytics on adjacent rows that have "9" in column B and "5" in the cell below it.

Thanks a lot!

 

[mumps]

Try:

Sub HideRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("B2:B" & LastRow)
        If (rng = 9 And rng.Offset(1, 0) = 5) Or (rng = 5 And rng.Offset(-1, 0) = 9) Then
            Rows(rng.Row & ":" & rng.Row + 1).Hidden = False
        Else
            Rows(rng.Row).Hidden = True
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[thankyou]

beautiful. let me give that a shot. Looks like that took a lot of focus. Will follow up shortly. thanks so much

 

[thankyou]

yeah mumps, that was perfect. I like the way you think!
huge thanks.
PS sorry it took me about 30mins longer than I was thinking I might be, fumbling my way around, but I think that's the only way to learn this stuff and I was able to make my way through it. great job. appreciate you and hope you're finding the answers you're looking for here because I'll bet you're learning a lot every day too.
thanks again.