Cannot apply sorting to a range 's autofilter

Question

I have already defined the range and applied filter as follow.

Dim wb As Workbook: Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
With ws
   Dim tbl_rng As Range
   Set tbl_rng = .Range("$A$1:$H$1000")
   tbl_rng.AutoFilter ' Turn on autofilter
   tbl_rng.AutoFilter Field:=4, Criteria1:="=*AAA*", Operator:=xlAnd
   With tbl_rng.AutoFilter.Sort  '<- line of error
       .SortFields.Clear
       .SortFields.Add2 Key:=Range("B1:B1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .Header = xlYes
       .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
   End With
End With

Problem occurs with the with tbl_rng.AutoFilter.Sort statement, it complains object required. I tried With .sort, it ran without error but it didn't sort the column.

So what have I done wrong here ? Why can I reference tbl_rng.Autofilter ?Why is it not an object ? How can I reference the autofilter of the range i defined instead of referencing the autofilter of the worksheet, because I could have multiple autofilter tables on the same sheet.

Answer 1

Please, try the next adapted code. It firstly apply Sort and then AutoFilter:

Sub SortAutofilterRng()
 Dim wb As Workbook: Set wb = ThisWorkbook
 Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
 Dim tbl_rng As Range

 With ws
    If .AutoFilterMode Then .AutoFilterMode = False
    Set tbl_rng = .Range("$A$1:$H$1000")
   
    With tbl_rng
        .cells.Sort key1:=.Columns(2), Order1:=xlAscending, _
                            Orientation:=xlTopToBottom, Header:=xlYes
        .AutoFilter field:=4, Criteria1:="*AAA*", Operator:=xlFilterValues
    End With
 End With
End Sub

Next version works firstly filtering and after that sorting the filtered range:

Private Sub AutofilterSortRng() 
 Dim wb As Workbook: Set wb = ThisWorkbook
 Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
 Dim tbl_rng As Range

 With ws
    If .AutoFilterMode Then .AutoFilterMode = False
    Set tbl_rng = .Range("$A$1:$H$1000")
    
    With tbl_rng
        .AutoFilter field:=4, Criteria1:="*AAA*", Operator:=xlFilterValues
        ws.AutoFilter.Range.Sort key1:=.Columns(2), Order1:=xlAscending, _
                                 Orientation:=xlTopToBottom, Header:=xlYes 'need to filter the AutoFilter.Range...
       
    End With
 End With
End Sub

And the next one is able to sort a pre existing filtered range:

Private Sub ExistingAutofilteredSortRng() 
 Dim wb As Workbook: Set wb = ThisWorkbook
 Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
 Dim tbl_rng As Range, autoFilt As Range

 With ws
    If Not .AutoFilterMode Then Exit Sub 'no any filter applied...
    Set tbl_rng = .Range("$A$1:$H$1000")
    
    With tbl_rng
       ' .AutoFilter field:=4, Criteria1:="*AAA*", Operator:=xlFilterValues
        ws.AutoFilter.Range.Sort key1:=.Columns(2), Order1:=xlAscending, _
                                 Orientation:=xlTopToBottom, Header:=xlYes
       
    End With
 End With
End Sub