VBA Autofilter code with leading zeroes

  • Hi,


    I'm trying to write a macro to autofilter two sheets in a workbook.


    The first filter is based on a value in a dropdown list. Within the dropdown list, there are a series of 4 digit codes some of which begin with a zero (e.g. 0112, 4568, 9999, 0765, etc).


    The second filter is in another sheet and it's simply trying to filter out all values that aren't zero.


    Here's what I have so far but it's not working.....


    Code
    Dim CostCentre As Integer
    CostCentre = Worksheets("Instructions").Range("A6").Value
    Sheets("Sheet1").Select
    ActiveSheet.Range("$A$1:$AS$5000").AutoFilter Field:=1, Criteria1:=CostCentre, _
    Operator:=xlAnd
    Sheets("Sheet2").Select
    ActiveSheet.Range("$A$1:$AN$85").AutoFilter Field:=40, Criteria1:="<>0", Operator:= _
    xlFilterValues
    End Sub



    Thanks!

    Edited once, last by Carim: Added Code Tags ().

  • Code
    Sub Maybe_So()
    With Sheets("Sheet1").Range("A1").CurrentRegion
    .AutoFilter 1, Worksheets("Instructions").Range("A6")
    With Sheets("Sheet2").Range("A1").CurrentRegion
    .AutoFilter 40, "<>0"
    End With
    End With
    End Sub


    BTW, if your drop down list values have a leading 0 (zero) they are not numerical values but text (String).

    So "CostCenter" would not be an Integer but a String.

  • "The second filter is in another sheet and it's simply trying to filter out all values that aren't zero."

    Which sheet is this in your attachment?


    "Worksheets("Instructions").Range("A6").Value"

    Where is this sheet in your attachment?


    Attach a representative workbook.

  • Try this, all cells containing the numbers must be formatted as Text, including on the Start Sheet


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!