VBA : How to Sort Data

This tutorial explains how to sort data in Excel using VBA.

You can download the following dataset to practice.

  Download Excel File

We can use the following VBA code to sort data in ascending or descending order.

Sub SortSingleColumnAscending()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") 
    ws.Range("A1:B10").Sort Key1:=ws.Range("A2:A10"), Order1:=xlAscending, Header:=xlYes
End Sub

1. Range(“A1:B10”) : It defines the range of cells that we want to sort.
2. Key1:=Range(“A2:A10”) : It is the primary sorting key. The data will be sorted based on the values in the range (“A2:A10”).
3. Order1:=xlAscending : It specifies the sorting order. xlAscending sorts from smallest to largest whereas xlDescending sorts from largest to smallest.
4. Header:=xlYes : It considers the first row as a header and excludes it from the sorting whereas xlNo includes the header in the sorting.

Let us consider a few examples to understand the practical applications of Sort Method.

The following macro can be used to sort the product names in column A of dataset in ascending order :

Sub Ascending_A()
    Dim ws As Worksheet
    Dim lastRow As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ws.Range("A1:C" & lastRow).Sort Key1:=ws.Range("A2:A" & lastRow), Order1:=xlAscending, Header:=xlYes
    
End Sub

Press Run or F5 to run the above macro.

The following macro can be used to sort the product names (column A) and stock quantities (column C) in ascending order :

Sub Ascending_A_C()
    Dim ws As Worksheet
    Dim lastRow As Long

        Set ws = ThisWorkbook.Sheets("Sheet1")
    
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
        ws.Range("A1:C" & lastRow).Sort _
        Key1:=ws.Range("A2:A" & lastRow), Order1:=xlAscending, _
        Key2:=ws.Range("C2:C" & lastRow), Order2:=xlAscending, _
        Header:=xlYes
    
End Sub

Press Run or F5 to run the above macro.

The following macro can be used to copy the data from Sheet1 (source) to Sheet2 (destination) and sort column C in descending order :

Sub Descending_C()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim sourceRange As Range
    Dim destRange As Range
    Dim lastRow As Long
    
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    
    Set sourceRange = wsSource.Range("A1:C" & lastRow)
    Set destRange = wsDest.Range("A1")

    wsDest.Cells.Clear
    
    sourceRange.Copy Destination:=destRange
    wsDest.Range("A1:C" & lastRow).Sort Key1:=wsDest.Range("C2:C" & lastRow), Order1:=xlDescending, Header:=xlYes
    
    wsDest.Range("A1:C1").Columns.AutoFit
    wsDest.Range("A1:A" & lastRow).Rows.AutoFit
End Sub

Press Run or F5 to run the above macro.

1. wsDest.Cells.Clear : It clears any pre-written data in the destination range to avoid overwriting.
2. sourceRange.Copy Destination:=destRange : It copies the data from the source to the destination.
3. Columns.AutoFit : It auto adjusts the column width.
4. Rows.AutoFit : It auto adjusts the row height.

Read MoreListenData