CODEWITHSUNDEEP
excelvba
Kes Perron
Kes Perron

Reputation: 477

VBA: Filtering and Sorting a pre-existing Autofilter range

Say I have an Excel worksheet which contains information about my music collection. I've written 2 macros: the first sorts the list by artist, album, and track number, and the second sorts the list by genre and song title. Here's what the macros look like:

ThisWorkbook.Sheets("Music").ShowAllData
With ThisWorkbook.Sheets("Music").AutoFilter.Sort
    .SortFields.Clear
    .SortFields.Add Range("A:A"), xlSortOnValues, xlAscending, , xlSortNormal
    .SortFields.Add Range("B:B"), xlSortOnValues, xlAscending, , xlSortNormal
    .SortFields.Add Range("C:C"), xlSortOnValues, xlAscending, , xlSortNormal
    .Header = xlYes
    .MatchCase = False
    .SortMethod = xlPinYin
    .Apply
End With

I want to add a line which filters a specific genre, like this:

ThisWorkbook.Sheets("Music").AutoFilter Field:=5, Criteria1:="Rock"

This line doesn't work because the Worksheet.AutoFilter method doesn't accept the same arguments as the Range.Autofilter method. Does anyone know a way around this that doesn't involve turning the filter off, figuring out the data range, applying a new filter, and then sorting that?

Upvotes: 1

Views: 3855

Answers (1)

simple-solution
simple-solution

Reputation: 1139

You may use any cellrange, even one without the range of the already defined autofilter.

Option Explicit

Sub add_filter_2_existing_autofilter()
Dim FilterRange As Range

    ThisWorkbook.Sheets("Music").Select

    'ShowAllData - error if no filter is set
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0

    'These do not work
    'ThisWorkbook.Sheets("Music").AutoFilter Field:=5, Criteria1:="Rock"
    'ThisWorkbook.Sheets("Music").FilterRange.AutoFilter Field:=5, Criteria1:="Rock", Operator:=xlFilterValues

    Set FilterRange = Sheets("Music").AutoFilter.Range
    FilterRange.AutoFilter Field:=5, Criteria1:="Rock", Operator:=xlFilterValues

    'OR one of the following equivalents, which also work:
    'ThisWorkbook.Sheets("Music").Range(FilterRange.Address).AutoFilter Field:=3, Criteria1:="Rock", Operator:=xlFilterValues
    'ThisWorkbook.Sheets("Music").Range("A1").AutoFilter Field:=5, Criteria1:="Rock", Operator:=xlFilterValues
    'ThisWorkbook.Sheets("Music").Range("A1000").AutoFilter Field:=5, Criteria1:="Rock", Operator:=xlFilterValues
    'ThisWorkbook.Sheets("Music").Range("XFD1").AutoFilter Field:=5, Criteria1:="Rock", Operator:=xlFilterValues

End Sub

Upvotes: 2

Related Questions