VBA Hide all rows from a key row downwards.

[ChrisMcIntyre]

Hi all,
Can anyone help me with the code to hide all rows downwards of a row I have a key word in please.

I have a worksheet that can have varying numbers of rows. I have hidden the word "Last" in column A301, which is the last row on the sheet in it's undedited form. What I want to do is trigger a code to hide all the rows from the one that has the word "Last" in it, downwards to the very bottom.

So as an example, if the row with the word "Last" in happens to be row 400 after the sheet has been edited (So "Last" is in A400) I need all rows from 401 to the very bottom all hidden when I click a macro button.

Hope I explained that well, apologiesif now, and I appreciate any help you can offer.
Thanks.

 

[ChrisMcIntyre]

Sorry bferraz, no joy with that either. The macro ran with no bugs but everything below the row with "LAST" in is still showing.

 

[ChrisMcIntyre]

Actually, it hid the row immediately underneath, as that is blank, but then the next rwo has something in and it appears to have stopped hiding at that point.

 

[bferraz]

Ohhh you have more rows under the Last. That is what is happening.

Please, try this one:

Sub HideRows()

Dim cl As Range
Dim sh As Worksheet
Dim rng As Range

Set sh = ActiveSheet

Set cl = sh.Range("A:A").Find(What:="Last", _
            After:=sh.Cells(1, 1), _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
            
        If Not cl Is Nothing Then
        
        Set rng = sh.Range(cl, sh.Range("A1048576"))
        rng.EntireRow.Hidden = True
        
        End If


End Sub

It should work as it won't stop at the next filled row.

 

[ChrisMcIntyre]

Oh LOVELY, that worked a treat! Thanks so much bferraz, and thanks too Georgiboy, Happy New Year to you both!

 

[bferraz]

Happy New Year!