Macro excel hide / unhide rows selected with matches

[Andrea_Ita]

Hello everybody,
I need help with an excel macro.. am getting crazy!

The Macro shall hide/unhide some rows when I click a button. The problem is that the rows numbers change, as usually I delete or insert new one. I think the Macro shall look in the first column of the sheet, find "text1", then find "tex2" (that indicate the beginning and the end of the section) and hide all the rows from "text1" to "text2" included..
Can anybody help me?

Thanks
Andrea

 

[Gavin T]

This will hide the rows between "Text1" & Text2".

Sub Macro1()

    Dim FirstRow As Long
    Dim LastRow  As Long

    FirstRow = Columns("A").Find("Text1").Row
    LastRow = Columns("A").Find("Text2").Row

    Cells.EntireRow.Hidden = False
    Rows(FirstRow & ":" & LastRow).Hidden = True

End Sub

 

[Andrea_Ita]

Thank a lot mate!!

 

[Gavin T]

You're welcome!

 

[Andrea_Ita]

I tried to make a button hide and unhide the rows but it didn't work..

Sub showhiderowHEM()
Dim FirstRow As Long
Dim LastRow As Long

FirstRow = Columns("B").Find("Text1").Row
LastRow = Columns("B").Find("Text2").Row

If Rows(FirstRow & ":" & LastRow).Hidden = True Then
Rows(FirstRow & ":" & LastRow).Hidden = False
Else
Rows(FirstRow & ":" & LastRow).Hidden = True
End If
End Sub

 

[Gavin T]

Did you assign the macro to a button? What didn't work? Did you get any errors?

Btw, instead of your if statement you can use this line:

Rows(FirstRow & ":" & LastRow).Hidden = Not Rows(FirstRow & ":" & LastRow).Hidden

 

[Andrea_Ita]

Hi Gavin,
Yes I assined the macro to a button but it didn't work.
when I run the macro it gives me this error:

Run-time error'91'
Object variable or With block variable not set

 

[Gavin T]

On which line of your code did the error occur?

 

[Andrea_Ita]

FirstRow = Columns("B").Find("Text1").Row

 

[Gavin T]

Are you sure the value "Text1" is in column B? If it can't find the value it would throw an error.