Please help with the following macro, sometimes it works but when it doesn't
I get the following error message "Runtime error '91 object variable or With
block variable not set". It stops on this row, rw1 = rng.Find(".", , ,
xlWhole).Row



Private Sub Worksheet_Activate()

Dim cell As Range
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Set rng = Sheets("income").Range("A9:A100")

rw1 = rng.Find(".", , , xlWhole).Row
rw2 = rng.Find("Funding Council grants", , , xlWhole).Row

Range(Cells(rw1, "A"), Cells(rw2 - 3, "A")).EntireRow.Hidden = True
Cells(1, "A").Select
Application.ScreenUpdating = True
End Sub

Sounds like the Find failed, Test for it

Private Sub Worksheet_Activate()
Dim rng As Range
Dim cell As Range
Dim rw1 As Long, rw2 As Long
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Set rng = Range("A9:A100")

On Error Resume Next
Set cell = rng.Find(".", , , xlWhole)
If cell Is Nothing Then
MsgBox "Dot not fopund"
Else
rw1 = cell.Row
Set cell = Nothing
Set cell = rng.Find("Funding Council grants", , , xlWhole)
If cell Is Nothing Then
MsgBox "Grants not found"
Else
rw2 = cell.Row
On Error GoTo 0
Range(Cells(rw1, "A"), Cells(rw2 - 3, "A")).EntireRow.Hidden = True
Cells(1, "A").Select
End If
End If
On Error GoTo 0
Application.ScreenUpdating = True
End Sub



--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"nc" wrote in message
...
Please help with the following macro, sometimes it works but when it
doesn't
I get the following error message "Runtime error '91 object variable or
With
block variable not set". It stops on this row, rw1 = rng.Find(".", , ,
xlWhole).Row



Private Sub Worksheet_Activate()

Dim cell As Range
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Set rng = Sheets("income").Range("A9:A100")

rw1 = rng.Find(".", , , xlWhole).Row
rw2 = rng.Find("Funding Council grants", , , xlWhole).Row

Range(Cells(rw1, "A"), Cells(rw2 - 3, "A")).EntireRow.Hidden = True
Cells(1, "A").Select
Application.ScreenUpdating = True
End Sub

Might have been grants vs Grants. Sheet not necessary if on that sheet, etc.

Private Sub Worksheet_Activate()
Rows.Hidden = False
Set rng = Range("a9:a100")
r1 = rng.Find(".").Row
r2 = rng.Find(UCase("FUNDING COUNCIL GRANTS")).Row - 3
Rows(r1 & ":" & r2).Hidden = True
Cells(1, 1).Select
End Sub

--
Don Guillett
SalesAid Software

"nc" wrote in message
...
Please help with the following macro, sometimes it works but when it
doesn't
I get the following error message "Runtime error '91 object variable or
With
block variable not set". It stops on this row, rw1 = rng.Find(".", , ,
xlWhole).Row



Private Sub Worksheet_Activate()

Dim cell As Range
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Set rng = Sheets("income").Range("A9:A100")

rw1 = rng.Find(".", , , xlWhole).Row
rw2 = rng.Find("Funding Council grants", , , xlWhole).Row

Range(Cells(rw1, "A"), Cells(rw2 - 3, "A")).EntireRow.Hidden = True
Cells(1, "A").Select
Application.ScreenUpdating = True
End Sub

Thanks for replying to my query. I managed to pin point the problem.

The following statement needs to be changed to

rw1 = rng.Find(".", , xlValues, xlWhole)

The range contains a function.


"nc" wrote:

Please help with the following macro, sometimes it works but when it doesn't
I get the following error message "Runtime error '91 object variable or With
block variable not set". It stops on this row, rw1 = rng.Find(".", , ,
xlWhole).Row



Private Sub Worksheet_Activate()

Dim cell As Range
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Set rng = Sheets("income").Range("A9:A100")

rw1 = rng.Find(".", , , xlWhole).Row
rw2 = rng.Find("Funding Council grants", , , xlWhole).Row

Range(Cells(rw1, "A"), Cells(rw2 - 3, "A")).EntireRow.Hidden = True
Cells(1, "A").Select
Application.ScreenUpdating = True
End Sub