Find method fails in hidden range

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

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

Jim Thomlinson


"Rick Hansen" wrote:

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

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


"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Rick Hansen" wrote:
>
> >
> > 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
> >
> >
> >