Find a hidden entry

[Otto Moehrbach]

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

 

[Don Guillett]

try changing

LookIn:=xlValues
to
LookIn:=xlformulas

-
Don Guillett
SalesAid Software
(e-mail address removed)

 

[Bob Phillips]

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]

Don, Bob
Thanks for both tips. They both work just fine. Otto

 

[Don Guillett]

we're glad to help

--
Don Guillett
SalesAid Software
(e-mail address removed)

Don, Bob
Thanks for both tips. They both work just fine. Otto

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