Forum Discussion
Find cells hidden by filter
I never realized but FIND (and the equivalent function in VBA) only looks at the selected cells of a Filter. I can see no option to change that behaviour. Does anyone know how to search all cells (ignore the Filter)? Thanks.
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 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.
