Find a hidden entry

O

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
 
B

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

Don Guillett

we're glad to help

--
Don Guillett
SalesAid Software
[email protected]
Otto Moehrbach said:
Don, Bob
Thanks for both tips. They both work just fine. Otto
Otto Moehrbach said:
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
Click to expand...
Click to expand...
 
Top