VBA : How to Sort Data

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

You can download the following dataset to practice.

Syntax of Sort Method

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

Range("A1:B10") : It defines the range of cells that we want to sort.
Key1:=Range("A2:A10") : It is the primary sorting key. The data will be sorted based on the values in the range ("A2:A10").
Order1:=xlAscending : It specifies the sorting order. xlAscending sorts from smallest to largest whereas xlDescending sorts from largest to smallest.
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.

1. Sort Column

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.

"Product 10" Comes Before "Product 2"

"Product 10" is considered to be smaller than "Product 2" because '1' in "10" comes before the '2' in "2"

Sort Column in Descending Order

Use Order1:=xlDescending in the above code to sort a column in descending order.

2. Sort Multiple Columns

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.

VBA : Sorting multiple columns using VBA

Sort Multiple Columns in Descending Order

In the above code, use Order:=xlDescending for each key to sort multiple columns in descending order.

3. Sort Data in Another Sheet

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.

VBA : Copy and Sort the data to Another Location

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

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn