Autofilter Excel 2003 not filtering for zero

Question

I have what I think is a very simple problem, setting up an autofilter to filter for zero, that I just can't get to work. I have built a macro enabled workbook in Excel 2007 that needs to be compatible with 2003 excel. In it, I run the following code to apply and set an autofilter on the currently selected sheet.

Range("A5:E5").AutoFilter field:=3, Criteria1:=0

This works as expected in 2007. When testing on a 2003 machine, nothing is filtered in the Criteria1 field - all rows remain unfiltered/visible - but no error is given. Any reason why?

of course... can't believe I didn't think to record the macro in '03. For what its worth, it needed to be Criteria1:="0" — Lindsay

Lindsay Lindsay · Accepted Answer · 2015-08-24T02:13:33

The Criteria1:="0" mentioned above didn't end up working, even though this is what an '03 machine recorded. I ended up getting around the issue using by checking the application version number and running different code (below), but wouldn't do so if the dataset is going to be large (I know this particular dataset won't be).

If Application.Version < 12 Then
            Range("ItemsTop").Select
            Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Select
            ActiveCell.Offset(0, 2).Select
            Do While ActiveCell.Row > 7
                If Selection.Value = 0 Then
                    ActiveCell.EntireRow.Delete
                End If
                ActiveCell.Offset(-1, 0).Select
            Loop
            'Range("A5:E5").AutoFilter field:=3, Criteria1:="0"
        Else
            Range("A5:E5").AutoFilter field:=3, Criteria1:=0
            Range("A5").Select
            ActiveCell.Offset(1, 0).Select
            Do Until ActiveCell.EntireRow.Hidden = False
                ActiveCell.Offset(1, 0).Select
            Loop
            Selection.EntireRow.Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Delete shift:=xlUp
            Selection.AutoFilter
        End If

Autofilter Excel 2003 not filtering for zero

1 Answers