# VBA Hide all rows from a key row downwards.



## ChrisMcIntyre (Dec 28, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

Maybe:

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


----------



## ChrisMcIntyre (Dec 28, 2022)

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


----------



## bferraz (Dec 28, 2022)

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 (Dec 28, 2022)

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


----------



## Georgiboy (Dec 28, 2022)

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

The text of "Last" needs to match exactly


----------



## ChrisMcIntyre (Dec 28, 2022)

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


----------



## bferraz (Dec 28, 2022)

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
```


----------



## ChrisMcIntyre (Dec 28, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

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


----------



## bferraz (Dec 28, 2022)

Happy New Year!


----------

