Excel Array Filter

Question

I have data in four columns in Excel. Column three ("C") contains a string which has a country name, e.g "The name of the country is China". The string is not standardized hence I am unable to use string functions like left/right/mid. I am creating a macro to filter six countries (Egypt, USA, China, Russia, Japan and Uganda). So far I can get two countries using the following macro:

Sub Countries ()
   Activesheet.Range("A1:D1000").Autofilter Field:=3, Criteria1:=Array("*Japan*","*China*"),Operator:=xlFilterValues
End Sub

This is working and I am able to get the two countries filtered. However when I add more countries the Excel does not bring up the countries and returns no rows. I am not sure what I am not doing right and I am hoping someone can help me please

@JohnyL the code is a continuation in the array, like: Array("*Japan*","*China*","*USA*","*Russia*","*Egypt*",.....).... — Lex, May 20 '18 at 20:53
Interesting question. Doesn't seem to like more than 2 wildcarded items and if you add a third non wildcarded item it selects only that. — QHarr, May 20 '18 at 21:08
This may be of interest to you https://stackoverflow.com/questions/16602872/set-auto-filtering-multiple-wildcards?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa Apparently maximum of two direct wildcards per field in the AutoFilter method — QHarr, May 20 '18 at 21:13

DisplayName · Accepted Answer · 2018-05-20T21:09:44.817

0

you could loop through countries:

Option Explicit

Sub main()
    Dim filter As Variant
    Dim countriesRng As Range

    Set countriesRng = Range("A1") ' initial dummy range to use Union() without checking for 'countriesRng' being 'Nothing'
    With Range("A1:D" & cells(Rows.Count, "C").End(xlUp).row) ' reference columns A:D from row 1 down to column C last not empty row
        For Each filter In Array("Egypt", "USA", "China", "Russia", "Japan", "Uganda") ' loop through countries
            .AutoFilter Field:=3, Criteria1:="*" & filter & "*" ' filter current country
            If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then Set countriesRng = Union(countriesRng, .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible)) ' if any filtered cells other than header then add them to 'countriesRng'
        Next
       .parent.AutoFilterMode = False

        Set countriesRng = Intersect(countriesRng, Range("C:C")) ' get rid of the initial dummy range
        If Not countriesRng Is Nothing Then ' if any filtered country
            .Resize(.Rows.Count - 1).Offset(1).EntireRow.Hidden = True ' hide all records
            countriesRng.EntireRow.Hidden = False ' unhide ones with filtered countries
        End If
    End With
End Sub

edited May 20 '18 at 21:09

answered May 20 '18 at 20:37

DisplayName

13,283
2
11
19

Its working and I got the address of the countries from MsgBox. Is it possible to get these filtered in the Excel sheet? – Lex May 20 '18 at 21:04
see edited answer. if it solves your question you may consider marking it as accepted. thank you – DisplayName May 20 '18 at 21:10
With only one clarification on line If Application.WorksheetFunction....i am not quite getting what this line does especially the significance of 103? Thanks – Lex May 20 '18 at 21:34
Does the solution work for you? If so, why not accepting it? That line makes the counting of visible cells in the range – DisplayName May 20 '18 at 21:41
Thank you. I have accepted it as a solution though I'm getting "...votes by those with less than 15 reputation...." – Lex May 21 '18 at 03:30
To mark an answer as accepted, click on the check mark beside the answer to toggle it from greyed out to filled in – DisplayName May 21 '18 at 04:37
Got it. Thanks you – Lex May 28 '18 at 18:36

Excel Array Filter

1 Answers1