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

@ecovonrein 

 

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

 

Unfiltered:

Sample.png

Filtered:

Sample2.png

 

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.

@ecovonrein 

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:

 

Sample.png

I am puzzled. How do you even get your dialogue box to look like this? Which Excel is that? Mine looks like this:

ecovonrein_0-1680853537063.png

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.)

@ecovonrein 

I run 365

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

 

Formatted as Table:

Sample.png

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

Sample_1.png

 

Formatted as Table:

Sample2.png

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.