Find Value in Hidden Cells

[Josh Sale]

It appears that Range.Find doesn't search cells in hidden rows or columns of
the specified range.

Does somebody have a clever way of getting around this restriction? I know
I can loop through each cell in the range and perform my own comparison.
However the range can be quite large and so I'd rather not search it in a
VBA loop.

TIA,

josh

 

[Josh Sale]

Never mind. I just did a Google search and found a suggestion to use
xlFormula instead of xlValues and that took care of me.

 

[Arvi Laanemets]

Hi

An example with column A hidden on worksheet:

Public Sub test()
x = 1
If Range("A1:A100").Find(x) Is Nothing Then
y = 0
Else
y = Range("A1:A100").Find(x).Row
End If
MsgBox (Iif(y=0,"No " & x & " was found",x & " was found on row " & y))
End Sub


Arvi Laanemets

 

[Nigel]

use neither and it will search hidden cells eg

Set c = Range("A1:B20").Find("FRED")

 

[Josh Sale]

I'm guessing that your solution depends on the LookIn argument defaulting to
xlFormulas?

 

[Tom Ogilvy]

That is correct. Specifying nothing is a crap shoot. the value is
persistent and depends on what the setting was the last FIND was used/this
value was specified in code or manually. See the VBA help on the Find
Method. Disregard this advice.

 

[Josh Sale]

Consider it ignored.

Thanks Tom.