Find Method To Search On Hidden Rows

[IanDunnett]

Hi Guys, hope this should be a quick one,

I'm using the Find function in VBA on a column of data, but the range is being set to Nothing if the data item I'm looking for happens to be in a row that is hidden at the time.

How do I set the find to look in all rows, hidden or not?
Can this be done without unhiding all the rows first?

Thanks,
Ian

[Andy Pope]

What code are you using?

[IanDunnett]

Hi Andy,

it's pretty simple really...

AcRng in dim'd as a range and AcNum is dim'd as as integer with some checks done to make sure it's a number.

Column B on the sheet "Action Log" has numbers in it but some of the rows are hidden.
I am finding that if the row with the number I'm looking for is hidden, AcRng is retunred as Nothing.

Set AcRng = Sheets("Action Log").Columns(2).Find(AcNum, lookat:=xlWhole, LookIn:=xlValues, _ 
after:=Sheets("Action Log").Cells(8, 2)) 

Thanks,
Ian

[Andy Pope]

If the LookIn argument is xlFormula the find will work on hidden cells.

If not you will need to unhide first. Or could you use the match function to search for a row?

[IanDunnett]

Thanks Andy. Strange solution, but it works in this instance.

[nekomatic]

If the LookIn argument is xlFormula the find will work on hidden cells.

Thanks for the tip, this solved my problem too - if only the VBA help thought to mention this!

I think you mean xlFormulas though.