Find method fails in hidden range

[Rick Hansen]

This is probably a question for for one Excel MVP's. I'm trying to find a
sub string in hidden range using the the Find method. If the sub string is
found the code is to make that Entire Row visible again. But everything I
have tried has failed. If I unhide the range everything works fines with
find method. My question is; Is it possible to use hidden range and still
manibulate data with in the hidden range, ie perform searches, change cell
formulas.......
I am using Excel 2000. A copy of test is below. Thank you time....

Rick


Option Explicit
Sub HideProjectRows()
Dim srcRng As Range, find As Range
Dim Str As String, FirstAddress As String
Dim lastRow As Long

Str = "899"
lastRow = Range("J2").End(xlDown).Row
Set srcRng = Range("J2:J" & lastRow)

srcRng.EntireRow.Hidden = True
Set find = srcRng.find(what:=Str, LookIn:=xlValues, lookat:=xlPart)
If Not find Is Nothing Then
FirstAddress = find.Address
Do
find.EntireRow.Hidden = False
find.EntireRow.Interior.ColorIndex = 54

Set find = srcRng.FindNext(find)

Loop While (Not find Is Nothing And find.Address <> FirstAddress)

End If

End Sub

 

[Guest]

I am not quite an MVP but maybe one day... Give this a try. Oddly enough it
has to be set to xlFormula and not xlValues. Also you had set "find as
range", but find is a reserved word so I changed it to rngFound...

Sub HideProjectRows()
Dim srcRng As Range, rngFound As Range
Dim Str As String, FirstAddress As String
Dim lastRow As Long

Str = "899"
lastRow = Range("J2").End(xlDown).Row
Set srcRng = Range("J2:J" & lastRow)
srcRng.EntireRow.Hidden = True
Set rngFound = srcRng.find(What:=Str, LookIn:=xlFormulas, Lookat:=xlPart)
If Not rngFound Is Nothing Then
FirstAddress = rngFound.Address
Do
rngFound.EntireRow.Hidden = False
rngFound.EntireRow.Interior.ColorIndex = 5
Set rngFound = srcRng.FindNext(rngFound)
Loop Until rngFound.Address = FirstAddress
End If
End Sub

 

[Rick Hansen]

Thank You very much Sir. I got caught by one of easy faults, using as
resevered word. Thank you also for the other settle code changes. Have
great Day...

Rick