Find cells hidden by filter

Iron Contributor

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.

7 Replies



Not top clear what you mean with Filter: FILTER function, Filter functionnality on the Home tab, Filter functionnality on a Table??? Assuming Table...







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. 

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.


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 run 365

I'm puzzeled too but for another reason...


Formatted as Table:


If I filter the table to show a only and do a Find All jones, no problem I get:



Formatted as Table:


If I filter the table to show a only and do Find All 500, no problem too


However, with the same setup and the table filtered to show a only, if I select the whole column A and do a Find All 500 (or Jones with 1st table) ==> NOT FOUND


Also, if instead of formatting as Table I activate the Home/Filter functionnality, filter the column to show a only and do a Find All (w/o selecting the column) ==> NOT FOUND too


The search results inside the Home/Find pop-up appear to be a feature of a structured Table. When I use such, I get your picture too. But it makes no difference to the result: Find All will not list any records hidden by the Filter setting.