Do NOT find in hidden rows?

[Maury Markowitz]

I have a very large spreadsheet that contains a number of inter-row
formulas. A number of rows have to be included in order to make the
formulas work, but should not be displayed on-screen. We simply hide
these rows, which to our users effectively makes them disappear.

I found, much to my dismay, that hidden rows do not get searched in
code lookups using .Find. No one was able to offer an explanation for
why this would be or how to avoid it, so I resorted to unhiding every
row, doing the find, then re-hiding. Yuck.

Now I find to my equal dismay that the interactive Find command DOES
search in hidden rows. This is precisely the opposite behavior that we
want.

So is there any way to make this skip hidden rows? Is there a pref
somewhere we can set?

Maury

 

[Peter T]

Manually, select the overall range you are interested in (or a single cell
for entire sheet), F5, Special cells, Visible cells, then do your find.

Code along similar lines though you can use the Intersect method.

Regards,
Peter T

 

[Dave Peterson]

In my experience, I've found that excel will find values in manually hidden rows
-- both via the userinterface and via code.

I wonder if your code isn't specifying all the parms that you need to make it
work. If you don't specify all the parms, then your code will inherit those
settings that the last user (or last piece of code) used.

(This may not help, but excel seems to ignore any rows that were hidden via
autofilter.)

And maybe your code could do the search, check to see if the found value was in
a cell where the row was hidden

if foundcell.entirerow.hidden = true then
'keep looking
else
'found it!
end if

 

[Maury Markowitz]

I could likely emulate what I'm trying to do in code, but is there a
way to have my code run when the user hits Find and then still call
back into the Find dialog?

I don't think it's an inheritance issue. When I use .Find in code it
definitely skips any hidden rows, but when I Control-F and type in the
exact same string it goes directly to the hidden row.

Very annoying.

Maury

 

[Dave Peterson]

You can show the edit|find dialog:

application.dialogs(xldialogformulafind).show