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
    1. Dim CostCentre As Integer
    2. CostCentre = Worksheets("Instructions").Range("A6").Value
    3. Sheets("Sheet1").Select
    4. ActiveSheet.Range("$A$1:$AS$5000").AutoFilter Field:=1, Criteria1:=CostCentre, _
    5. Operator:=xlAnd
    6. Sheets("Sheet2").Select
    7. ActiveSheet.Range("$A$1:$AN$85").AutoFilter Field:=40, Criteria1:="<>0", Operator:= _
    8. xlFilterValues
    9. End Sub



    Thanks!

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

  • Code
    1. Sub Maybe_So()
    2. With Sheets("Sheet1").Range("A1").CurrentRegion
    3. .AutoFilter 1, Worksheets("Instructions").Range("A6")
    4. With Sheets("Sheet2").Range("A1").CurrentRegion
    5. .AutoFilter 40, "<>0"
    6. End With
    7. End With
    8. 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