CODEWITHSUNDEEP
vbaexcelfilter
adp
adp

Reputation: 311

Filter numeric field

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?

Upvotes: 1

Views: 9526

Answers (2)

pnuts
pnuts

Reputation: 59460

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.

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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

Upvotes: 2

Related Questions