Table of Contents
Sorting by date in VBA can be done using the Sort method of the Range object. A Range is a collection of cells. For example, if we wanted to sort a range of cells A1:A10 by date, we could use the following code: Range(“A1:A10”).Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlYes. This would sort the cells in range A1:A10 by date in ascending order with the first row being interpreted as a header.
You can use the following basic syntax in VBA to sort rows by date:
Sub SortByDate()
Range("A1:C10").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
End Sub
This particular example sorts the rows in the range A1:C10 by the dates in column A from earliest to latest.
If you’d like to sort the rows by date from latest to earliest then you can specify Order1:=xlDescending instead.
Note that Header:=xlYes specifies that the first row should be treated as a header row.
The following example shows how to use this syntax in practice.
Example: Sort By Date Using VBA
Suppose we have the following dataset in Excel that contains information about sales and refunds at some store on various dates:
Suppose we would like to sort the rows by date from earliest to latest.
We can create the following macro to do so:
Sub SortByDate()
Range("A1:C10").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
End Sub
When we run this macro, we receive the following output:
Notice that the rows are now sorted by date from earliest to latest date.
To instead sort the rows by date from latest to earliest, we we can specify Order1:=xlDescending:
Sub SortByDate()
Range("A1:C10").Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlYes
End Sub
Notice that the rows are now sorted by date from latest to earliest date.
Note #1: In this example we sorted by one column. However, you can specify more Keys to sort by multiple columns.
Note #2: You can find the complete documentation for the VBA Sort method .