Menu
I have a computed range of values in a hidden column which I use for a dropdown box. To figure out which value the user has selected, I try to run a Find on that range, but for some reason Excel won't return the cell corresponding with their selection so long as the column is hidden.
How can I get Find working on cells in the hidden range. Remember - I'm searching cell calculated values, not formulas.
The following does not work:
Set inserted = Range("RDS_Event_IDs").Find(Range("SelectedEvent"), , xlValues, xlWhole)
so long as cells in Range("RDS_Event_IDs") is hidden.
Because the solution has to work in general situations, where some or all of the the range being searched might be hidden, and the entire sheet might be searched, it isn't feasible to programmatically un-hide all affected rows and columns and then re-hide the ones that were previously hidden.
953
On the Home tab, in the Cells group, click Format. Do one of the following: Under Visibility, click Hide & Unhide, and then click Unhide Rows or Unhide Columns.
5 Answers. Show activity on this post. As of today, you can do a simple Ctrl - F and find it across sheets. In case it's hidden, you get a notification saying that the search result matches a hidden cell.
Hiding Cell ContentsYou have the ability to hide the contents of individual cells if you do not need to view their contents or you simply do not want to print certain cells. Select the cell(s) to be hidden. From the Home command tab, in the Cells group, click Format » select Format Cells...
According to Andy Pope (and he's never wrong) Find only works on hidden cells if you're using xlFormulas. Perhaps a Match instead?
Set inserted = Cells(Application.WorksheetFunction.Match("SelectedEvent", Range("RDS_Event_IDs"), 0), Range("RDS_Event_IDs").Column)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
