VBA sorting for flexible table

wktay When sorting a standard range on a worksheet, you can use either Worksheet.Sort or the Range.Sort method. The Range.Sort method is easier to use, but only allows up to 3 sort fields (keys). The follow examples outline how both methods can be used to sort an ever-expanding range of data that begins in cell A1:

Example 1: Worksheet.Sort

Option Explicit

Sub WorksheetSortMethod()

'Find the last row in Column A and last column in Row 1
    Dim ws As Worksheet, lastRow As Long, lastCol As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

'Sort the data range by columns 1 and 2
    Dim rg As Range, rgData As Range
    Set rg = ws.Range("A1").Resize(lastRow, lastCol)
    Set rgData = rg.Offset(1).Resize(rg.Rows.Count - 1)
    With ws.Sort.SortFields
        .Clear
        .Add2 Key:=rgData.Columns(1), Order:=xlAscending
        .Add2 Key:=rgData.Columns(2), Order:=xlAscending
    End With
    With ws.Sort
        .SetRange rg
        .Header = xlYes
        .Apply
    End With

End Sub

Example 2: Range.Sort

Option Explicit

Sub RangeSortMethod()

'Find last row in Column A and last column in Row 1
    Dim ws As Worksheet, lastRow As Long, lastCol As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

'Sort the data range by columns 1 and 2
    Dim rg As Range
    Set rg = ws.Range("A1").Resize(lastRow, lastCol)
    rg.Sort Key1:=rg.Columns(1), Order1:=xlAscending, _
        Key2:=rg.Columns(2), Order2:=xlAscending, Header:=xlYes

End Sub

Note: there are various ways to identify the entire data range (ie: Range.CurrentRegion, Worksheet.UsedRange, etc.) or to find the last row/column in a range. Choosing the right one may vary from one scenario to the next. If you would like to learn more, please see Excel Macro Mastery's YouTube channel: How to get the Last Row in VBA(The Right Way!)