Find a hidden entry
 

Excel 2002, WinXP
The code below is a simplification of my code to demonstrate my problem.
I have a, b, c, d, e in A1:A5.
Rows 3:5 are hidden rows
I want to search the range A1:A5, in VBA, to find the row number of the "c"
entry.
This code will not work because Row 3, the "c" row, is hidden.
My intent is to find the row, unhide it, and work with it.
Question: Is there a way to find the row number of the "c" entry, short of
unhiding the whole Rng first?
One way is to loop through Rng and look for "c". Is there a better way?
Thanks for your help. Otto

Sub FindHidden()
Dim Rng As Range
Dim c As Long
Set Rng = [A1:A5]
c = Rng.Find(What:="c", _
After:=[A1], LookIn:=xlValues, LookAt:=xlWhole).Row
MsgBox c
End Sub

Find a hidden entry
 

try changing

LookIn:=xlValues
to
LookIn:=xlformulas

-
Don Guillett
SalesAid Software

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
The code below is a simplification of my code to demonstrate my problem.
I have a, b, c, d, e in A1:A5.
Rows 3:5 are hidden rows
I want to search the range A1:A5, in VBA, to find the row number of the
"c"
entry.
This code will not work because Row 3, the "c" row, is hidden.
My intent is to find the row, unhide it, and work with it.
Question: Is there a way to find the row number of the "c" entry, short
of
unhiding the whole Rng first?
One way is to loop through Rng and look for "c". Is there a better way?
Thanks for your help. Otto

Sub FindHidden()
Dim Rng As Range
Dim c As Long
Set Rng = [A1:A5]
c = Rng.Find(What:="c", _
After:=[A1], LookIn:=xlValues, LookAt:=xlWhole).Row
MsgBox c
End Sub

Find a hidden entry
 

Hi Otto,

Worksheetfunction Match doesn't care about hidden rows

Dim Rng As Range
Dim c As Long
Set Rng = Range("A1:A5")
c = WorksheetFunction.Match("c", Rng, 0)
MsgBox c


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
The code below is a simplification of my code to demonstrate my problem.
I have a, b, c, d, e in A1:A5.
Rows 3:5 are hidden rows
I want to search the range A1:A5, in VBA, to find the row number of the
"c"
entry.
This code will not work because Row 3, the "c" row, is hidden.
My intent is to find the row, unhide it, and work with it.
Question: Is there a way to find the row number of the "c" entry, short
of
unhiding the whole Rng first?
One way is to loop through Rng and look for "c". Is there a better way?
Thanks for your help. Otto

Sub FindHidden()
Dim Rng As Range
Dim c As Long
Set Rng = [A1:A5]
c = Rng.Find(What:="c", _
After:=[A1], LookIn:=xlValues, LookAt:=xlWhole).Row
MsgBox c
End Sub

Find a hidden entry
 

Don, Bob
Thanks for both tips. They both work just fine. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
The code below is a simplification of my code to demonstrate my problem.
I have a, b, c, d, e in A1:A5.
Rows 3:5 are hidden rows
I want to search the range A1:A5, in VBA, to find the row number of the
"c"
entry.
This code will not work because Row 3, the "c" row, is hidden.
My intent is to find the row, unhide it, and work with it.
Question: Is there a way to find the row number of the "c" entry, short
of
unhiding the whole Rng first?
One way is to loop through Rng and look for "c". Is there a better way?
Thanks for your help. Otto

Sub FindHidden()
Dim Rng As Range
Dim c As Long
Set Rng = [A1:A5]
c = Rng.Find(What:="c", _
After:=[A1], LookIn:=xlValues, LookAt:=xlWhole).Row
MsgBox c
End Sub

Find a hidden entry
 

we're glad to help

--
Don Guillett
SalesAid Software

"Otto Moehrbach" wrote in message
...
Don, Bob
Thanks for both tips. They both work just fine. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
The code below is a simplification of my code to demonstrate my problem.
I have a, b, c, d, e in A1:A5.
Rows 3:5 are hidden rows
I want to search the range A1:A5, in VBA, to find the row number of the
"c"
entry.
This code will not work because Row 3, the "c" row, is hidden.
My intent is to find the row, unhide it, and work with it.
Question: Is there a way to find the row number of the "c" entry, short
of
unhiding the whole Rng first?
One way is to loop through Rng and look for "c". Is there a better way?
Thanks for your help. Otto

Sub FindHidden()
Dim Rng As Range
Dim c As Long
Set Rng = [A1:A5]
c = Rng.Find(What:="c", _
After:=[A1], LookIn:=xlValues, LookAt:=xlWhole).Row
MsgBox c
End Sub