Filter numeric field

Question

I have the following code:

ActiveSheet.Range("$A$1:$P$201").AutoFilter Field:=5, Criteria1:="=10"* 

When I click on the filter and type in the search bar 10* I get all results that start with 10. When using the macro, that doesn't work. The goal is for the macro to filter using the first two numbers provided by me.

Can you assist?

Answer 1

The core of the problem seems to have been trying to apply a text filter to a numeric field. Instead of:

ActiveSheet.Range("$A$1:$P$201").AutoFilter Field:=5 ActiveSheet.Range("$A$1:$P$201").AutoFilter Field:=5, Criteria1:="=10"*

just:

ActiveSheet.Range("$A$1:$P$201").AutoFilter Field:=5, _  
    Criteria1:=">=10000", Operator:=xlAnd, Criteria2:="<=10999"

seems to have worked.

Answer 2

The following will work only if the values are text:

Sub Macro2()
    ActiveSheet.Range("$A$1:$P$201").AutoFilter Field:=5, Criteria1:="=10*", _
        Operator:=xlAnd
End Sub

If the values are not text, then use a "helper" column.

EDIT#1:

For postal codes in column E, this will filter out (hide) rows not containing "10*" codes:

Sub GoingPostal()
    Dim r As Range
    For Each r In Range("E2:E201")
        st = Left(r.Text, 2)
        If st <> "10" Then
            r.EntireRow.Hidden = True
        End If
    Next r
End Sub