AdvancedFilter Searching VBA



I like to search a name and show them in my results row on sheet 2 that works fine.

But it seems when found i get duplicated names?

So my question will be

How can we when found the results to show 1 name only if already exists.

If not searching works fine but i like to fix the duplicated.

Thank you



Selected Answer


The filtering works as expected (I. e. you will get several rows if the search criteria are met by those rows).

If I understand you correctly, you don't wanrt to see Ash appear twice if there  are two rows including Ash.

Assuming the CODE item is variable but the Customer ID and Name are linked, you can filter the data but then bring just those two columns to Sheet1 then use the Range.RemoveDuplicates method to remove duplicates of name (so column 2 of the reduced range) . Change the last lines of your code to read:

    Sheet2.Range("D7:E" & LastResultRow + 1).Value = .Range("W6:X" & LastResultRow).Value 'Bring over search results for first 2 columns only
    Sheet2.Range("D7:E" & LastResultRow + 1).RemoveDuplicates Columns:=2, Header:=xlNo 'remove duplicates in column2 of the range

End With
where changes are shown in bold.

If you search for just "h" then you will get several names (each containing h but once only). 

Hope this is the result you were aiming for.



Hi John_Ru

Awesome, thanks that's what i needed ;)
GhostofWanted (rep: 34) Jul 24, '21 at 12:21 am
Great, thanks Ghost. Note that I just corrected several typos in my Answer (plus your nickname!).
John_Ru (rep: 1917) Jul 24, '21 at 1:40 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login