Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do NOT find in hidden rows?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do NOT find in hidden rows?
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 "Maury Markowitz" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do NOT find in hidden rows?
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 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do NOT find in hidden rows?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do NOT find in hidden rows?
You can show the edit|find dialog:
application.dialogs(xldialogformulafind).show Maury Markowitz wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find last row with hidden rows | Excel Discussion (Misc queries) | |||
Rows hidden by Autofilter vs hidden by changing the Hidden property | Excel Programming | |||
Formula or Code to keep Hidden Rows Hidden | Excel Worksheet Functions | |||
Find Hidden rows and delete | Excel Programming | |||
I need my Hidden Rows to stay hidden when I print the sheet. | Excel Discussion (Misc queries) |