vba - Excel filter a column by the first letters for more than 2 values

Question

I am very new at vba, and now fighting with one macro which will filter a Column by the first exact letters (for instance, I have a Column N - “City” and as a result I have to have all entries , starts for instance- “Vancouver”, “Vancouver. BC”, “Vancouver Canada” – so I want to sort this column by the first letters – VANCOU - to be sure, that I will not miss any info.

The code below does not work at all for 3 values – probably I choose a wrong way ., can you please advise – which function or operator will work at this case? All I find - work for 2 values (at that case I can use at list "begins with"). I have 5-6 values, and they might vary (I don't know which format of City name I will have next time) .

Thanks in advance!

Dim rng01 As Range
Set rng01 = [A1:Z5048]
    rng01.Parent.AutoFilterMode = False
    rng01.Columns(14).AutoFilter Field:=1, Criteria1:=Array("Vancou*", "Brampt*", "Halifa*"), Operator:= _
        xlFilterValues

Upd: Here is an adapted code , which is not working

Option Explicit
Sub AutoFilterWorkaround()

Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "N").End(xlUp).Row

'List the parts of the words you need to find here
tofindarr = Array("Vancou", "Brampt", "Halifa")

ReDim filterarr(0 To 0)
j = 0

For k = 0 To UBound(tofindarr)

    For i = 2 To lastrow
        If InStr(sht.Cells(i, 14).Value, tofindarr(k)) > 0 Then
            filterarr(j) = sht.Cells(i, 14).Value
            j = j + 1
            ReDim Preserve filterarr(0 To j)
        End If
    Next i

Next k

'Filter on array
sht.Range("$N$1:$N$" & lastrow).AutoFilter Field:=14, Criteria1:=Array(filterarr), Operator:=xlFilterValues

End Sub

Answer 1

Okay, so I rewrote the workaround - basically we avoid using wildcards by just finding each individual match case, loading that into an array, then filter on the entire array at the end.

This example works for column A - just change the A in lastrow to N, as well as changing the As to Ns in the last line. Also specify your sheet name on the Set sht line. Also Field:=1 needs to be changed to Field:=14 for column N in your case.

Option Explicit
Sub AutoFilterWorkaround()

Dim sht As Worksheet
Dim filterarr As Variant, tofindarr As Variant
Dim lastrow As Long, i As Long, j As Long, k As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'List the parts of the words you need to find here
tofindarr = Array("Vancou", "Brampt", "Halifa")

ReDim filterarr(0 To 0)
j = 0

For k = 0 To UBound(tofindarr)

    For i = 2 To lastrow
        If InStr(sht.Cells(i, 1).Value, tofindarr(k)) > 0 Then
            filterarr(j) = sht.Cells(i, 1).Value
            j = j + 1
            ReDim Preserve filterarr(0 To j)
        End If
    Next i

Next k

'Filter on array
sht.Range("$A$1:$A$" & lastrow).AutoFilter Field:=1, Criteria1:=Array(filterarr), Operator:=xlFilterValues

End Sub