CODEWITHSUNDEEP
excelvba
user2161965
user2161965

Reputation: 37

Why is AutoFilter being applied three rows before the specified range

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

Upvotes: 0

Views: 110

Answers (2)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Use the following syntax to avoid your problem:

Ws.Range("A6:e6").AutoFilter '... and so on with exact range address

Upvotes: 1

grahamj42
grahamj42

Reputation: 2762

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.

Upvotes: 1

Related Questions