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.

 

[bferraz]

Hey, can you try the code below?

Sub HideRows()

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

Set sh = ActiveSheet

Set cl = sh.Cells.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, cl.End(xlDown))
        rng.EntireRow.Hidden = True
        
        End If


End Sub

 

[ChrisMcIntyre]

Thanks for the speedy response bferraz!

It kind of worked, but it maybe hit some text I had underneath the word "Last" and so stopped hiding. I literally need it to search for the exact word "Last" in column A and then from the row below that word down, hide all rows.

 

[Georgiboy]

Maybe:

Sub test()
    Rows(Cells.Find("Last", , , xlWhole).Row & ":" & Rows.Count).Hidden = True
End Sub

 

[ChrisMcIntyre]

Thanks so much Georgiboy, unfortunately that bugged out...

 

[bferraz]

You are welcome! Can you provide more information as the text on the row which was hidden? The argument xlWhole should check for the full text of “Last”

 

[ChrisMcIntyre]

I have a bunch of cells with text in relating to other lookups, but I still need all those rows hidden.

 

[Georgiboy]

I get that error if I put a space on the end of "Last "

The text of "Last" needs to match exactly

 

[ChrisMcIntyre]

100% only have "Last" in that field, no extra spaces or additional characters

 

[bferraz]

Can you try this one? This one searchs for the whole text "Last" on Column A.

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, cl.End(xlDown))
        rng.EntireRow.Hidden = True
        
        End If


End Sub