Why is AutoFilter being applied three rows before the specified range

Question

The following code applies autofilter to several worksheets from date criteria set in worksheet "'Grand Totals'!B1", applying to worksheet range A6, which is formatted as date. Rows 1-5 contain headers and formulae. But, rows 3-5 are disappearing along with the filtered rows beginning in A6. Can anyone see why? According to what I know, everything above row A6 should remain visible. Thanks again for your help.

Sub ApplyFilterDate()

    Dim Ws As Worksheet
    Application.ScreenUpdating = False 'Turn off ScreenUpdating to speed filtering
        For Each Ws In ActiveWorkbook.Worksheets
            If Ws.Name <> "Grand Totals" Then
                Ws.Activate
                Ws.AutoFilterMode = False 'Remove any existing filters
                Ws.Range("A6").AutoFilter Field:=1, Criteria1:=Range("'Grand Totals'!B1").Text
                Range("G2").Activate
                Center_it 'Puts next data entry cell in approximate center of screen
            End If
        Next
    Sheet1.Activate
    Range("B2").ClearContents
    Range("B1").Interior.ColorIndex = 3 'Set color of cell showing filter date
    Range("B1").Activate
    Application.ScreenUpdating = True 'Turn on ScreenUpdating

End Sub

grahamj42 grahamj42 · Accepted Answer · 2013-03-26T20:08:30

You are asking the Autofilter to operate on cell A6 only. This doesn't make sense, so Excel expands the selection to include surrounding cells. You need to specify the range on which to apply the autofilter.

Why is AutoFilter being applied three rows before the specified range

2 Answers