Forum Discussion
Find cells hidden by filter
Not top clear what you mean with Filter: FILTER function, Filter functionnality on the Home tab, Filter functionnality on a Table??? Assuming Table...
Unfiltered:
Filtered:
in G2:
=XMATCH("*" & E2 & "*", Table1[Name], 2)
You assumed right (though I grant you the language is becoming increasingly ambiguous given the multitude of similarly named features). I did not write Table because you don't need a table to use Data/Filter 😉 (Perhaps Data/Filter helps to disambiguate.) And I am not trying to use any Excel functions; I am just trying to Home/Find an item of data (which I know exists but is hidden by the current settings of the Data/Filter). But Find won't find it (until I clear the Data/Filter, when suddenly - hey presto)...
PS: I am doing this all from VBA and a work-around I use until I hear of anything better is to instruct "aSheet.ShowAllData" (which is the equivalent of Data/Filter/Clear). It cures the problem with Find at the expense of resetting all Filters.
You assumed right... On the other hand I wrongly assumed re. the FIND function
Given that the row where JONES exists - with my example - is hidden it seems logical to me that Excel tells you it doesn't find it. However, if you do Find All you get the info:
I am puzzled. How do you even get your dialogue box to look like this? Which Excel is that? Mine looks like this:
The 500 exist in the line filtered away but clicking Find All does not make the slightest difference. (My Excel is trying to show hits by putting the cursor there, not by listing them in the pop-up.)
- I learned something new in the process: A row hidden by a Filter (which cannot be found by Find) is distinct from a row hidden by Hide (which will be found by Find). That is, Filter does not seem to use Hide as a means by which to arrive at its presentation.
