CODEWITHSUNDEEP
excelvba
Akinn
Akinn

Reputation: 2078

Search in filtered list

I have a simple worksheet A containing a list of strings under the same column. Moreover, from another sheet B I have a macro that searches for the former strings in the list. Everything works fine except for the case when a filter is applied to the list in worksheet A. In this case, the search acts like the elements filtered in the list don't exist.

Is there a way to refer to the original list without caring whether the list displayed is filtered or not?

The macro that access the list:

Worksheets("A").Range("A:A").Find(Cells(row, 1).value, LookIn:=xlValues, lookAt:=xlWhole, MatchCase:=True)

I gave a look into Find but I didn't find useful workarounds.

Upvotes: 1

Views: 88

Answers (2)

AcsErno
AcsErno

Reputation: 1615

An alternative to .Find and .Match is the classic cell by cell comparision:

lr = Range("a111111").End(xlUp).Row

For i = 1 To lr
    If Cells(i, 1) = <reference value> Then 
         <match found>
    End If
Next

This is a perfect alternative to .Find with LookIn:=xlValues, lookAt:=xlWhole, MatchCase:=True. It will check all cells in column A including hidden and filtered out cells. You will not even experience a slowdown under a few thousand rows.

Upvotes: 0

basodre
basodre

Reputation: 5770

The Application.Match() function returns the appropriate record even in cases where it is filtered away. The syntax would be:

Application.Match(cells(row, 1).value, Range("A:A"), 0)

Instead of returning a Range object, it will return a Long pointing to the row where the data was found, or an error if it isn't found. Perhaps this can be a workaround instead of using .Find()

Edit to clarify: The function does not return the row number of the matched cell, but rather the row offset of the range being searched. For example, if your code is Application.Match("ABC", Range("A5:A10"), 0), and the match is in the first cell of the range, A5, the function call returns the number 1, not 5.

Upvotes: 1

Related Questions