Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

VBA sorting for flexible table

Copper Contributor

Howdy all,

 

Just learning how to use VBA to do sorting

IndexNameBuyer
23AppleWilliam
56PearJone
49BananaMay
31DurianAndy

 

Above list is not fixed. There might be more rows and col added in future.

 

How would I write VBA code that can accommodate to flexible table size?

 

Public Sub Sort()
    With ActiveSheet.Sort
         .SortFields.Add Key:=Range("A1"), Order:=xlAscending
         .SortFields.Add Key:=Range("B1"), Order:=xlAscending
         .SetRange Range("A1", Range("A1").End(xlDown))
         .Header = xlYes
         .Apply
    End With
End Sub

 

 

 Much appreciated

6 Replies
instead of .SetRange Range("A1", Range("A1").End(xlDown))
use
.SetRange Range("A1").CurrentRegion

@Jan Karel Pieterse 

Thank you, but what if there is an empty row? Basically empty row should go to very bottom

 

IndexNameBuyer
23AppleWilliam
56PearJone
   
49BananaMay
31DurianAndy
best response confirmed by wktay (Copper Contributor)
Solution
Set uR = ActiveSheet.UsedRange

.SetRange uR

Empty rows within data are considered bad practice. Your data looks like a table, I advise to use Format as table, that will make this easier.

@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!) 

Thank you very much
1 best response

Accepted Solutions
best response confirmed by wktay (Copper Contributor)
Solution
Set uR = ActiveSheet.UsedRange

.SetRange uR

View solution in original post