Help with this macro

N

nc

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
 
B

Bob Phillips

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)
 
D

Don Guillett

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
 
N

nc

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.
 
Top