Excel VBA to Sort Column Ascending (9 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Sorting is frequently used by Excel users. The user, therefore, needs a handy and easy procedure to sort columns. For this purpose, VBA code can be very helpful. In this article, we will describe elaborately how you can use VBA to sort columns ascending. We will describe 9 examples that will help you while sorting your data in ascending order. In the following video, you can see the overview of the Excel VBA sort column ascending.

We hope you have a preview of what we are going to do in the following article. So let’s go!


Overview of Range.Sort Method

The Range.Sort method in VBA sorts a range of values in Excel. The range indicates the data range. If you are using data that is in cells B1:B10, then Range would be RANGE(“B1:B10”).

Instead of using data range, you can create a Named Range and use it as Range. For example, if you create a named range ‘Range of data’ for the cells B1:B10, then Range would be Range(“Range of Data”).

Below are the parameters you need to know while working with this method.

Range.Sort Method which is used to sort column in ascending order using VBA in Excel


Excel VBA to Sort Column in Ascending Order: 9 Cases

In the following article, we will describe 9 easy examples to sort columns ascending using VBA in Excel. All these examples will demonstrate real-life scenarios. Here, we used Excel 365. You can use any available Excel version.


1. Sorting a Single Column in Ascending Order

If you have a single column, and you want to sort the data of that column in ascending order using VBA, then this method is for you. Here, we will describe how you can sort a single column in ascending order using VBA in Excel. We will go through 2 examples of this, sorting a single column without a header, and sorting a single column with a header.


1.1 Sorting a Single Column Without a Header

In the following video, you can see that we have a single column with some Salary values. The column does not have a heading. The values of the column are in discrete order. Now, when we run the VBA code, it will sort the values of the column in ascending order in Excel. Further, we will describe how you can do so.

First of all, you will type the following code in  Module1.

Sub Sort_SingleColumn_WithoutHeader()
Range("B4:B14").Sort Key1:=Range("B4"), _
Order1:=xlAscending, Header:=xlNo
End Sub

Code Breakdown

Sub Sort_SingleColumn_WithoutHeader()

We take Sort_SingleColumn_WithoutHeader as the Sub procedure.

Range("B4:B14").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlNo

We take B4:B14 as Range, and we used Range.Sort method to sort this range.

Based on the value of B4 the range will be sorted.

The order for sorting will be an ascending order.

xlNo defines that the column has no header.

End Sub

Finally, we end the Sub procedure.

Finally, when you Run the code, the column will be sorted in ascending order like in the following picture.

Result after Sorting Single Column Without Header in ascending order in Excel VBA


1.2 Sorting a Single Column With a Header

In the following video, you can see that we have a single column with some Salary values. The column has a heading. The values of the column are in discrete order. Now, when we run the code, the values of the column are set in ascending order. Further, we will describe how you can do so.

First of all, we will type the following VBA code in Module2.

Sub Sort_SingleColumn_WithHeader()
Range("B4:B14").Sort Key1:=Range("B4"), _
Order1:=xlAscending, Header:=xlYes
End Sub

Code Breakdown

Sub Sort_SingleColumn_WithHeader()

We take Sort_SingleColumn_WithHeader as the Sub procedure.

Range("B4:B14").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes

We take B4:B14 as Range, and we used Range.Sort method to sort this range.

Based on the value of B4 the range will be sorted.

The order for sorting will be an ascending order.

xlYes defines that the column has a header.

End Sub

Finally, we end the Sub procedure.

Finally, when you Run the code, the column will be sorted in ascending order like in the following picture.

Result after Sorting Single Column With a Header in ascending order using VBA in Excel

Read More: VBA to Sort Column in Excel


2. Sort Multiple Columns of a Range Based on a Single Column with VBA in Excel

In the following video you can see that we have used VBA code to sort the Product Name, Price, and Purchase Date columns based on the Purchase Date in ascending order.

In the first place, we will type the following VBA code in Module3.

Sub Sort_MultipleColumns_Basedon_SingleColumn()
ActiveSheet.Range("B5:D13").Sort Key1:=Range("D8"), _
Order1:=xlAscending
End Sub

Code Breakdown

Sub Sort_MultipleColumns_Basedon_SingleColumn()

We take Sort_MultipleColumns_Basedon_Single_Column as the Sub procedure.

ActiveSheet.Range("B5:D13").Sort Key1:=Range("D8"), _
Order1:=xlAscending

We take B5:D13 as Range, and we used Range.Sort method to sort this range.

Based on the value of D8 the range will be sorted.

The order for sorting will be an ascending order.

End Sub

Finally, we end the Sub procedure.

Finally, when you Run the code, the columns will be sorted in ascending order based on column Purchase Date.

Sorting Multiple Columns of a Range Based on a Single Column in ascending order using VBA in Excel

Read More: How to Sort Multiple Columns with Excel VBA


3. Sort Multiple Columns of a Range Based on Multiple Columns with VBA in Excel

In the following video, you can see that we have sorted multiple columns in ascending order based on multiple columns with VBA. Here, we have sorted the dataset based on Price and Purchase Date. Therefore, you can see the price is in ascending order. Also, the Purchase Date is in ascending order. For two similar prices, you can easily notice that sorting occurred based on the Purchase Date. . let’s demonstrate to you how you can do this as well.

In the beginning, we will type the following VBA code in Module4.

Sub Sort_MultipleColumns_Based_on_MultipleColumns()
ActiveSheet.Range("B5:D13").Sort Key1:=Range("C8"), _
Order1:=xlAscending, Key2:=Range("D8"), Order2:=xlAscending
End Sub

Code Breakdown

Sub Sort_MultipleColumns_Based_on_MultipleColumns()

We take Sort_MultipleColumns_Based_on_MultipleColumns as the Sub procedure.

ActiveSheet.Range("B5:D13").Sort Key1:=Range("C8"), _
Order1:=xlAscending, Key2:=Range("D8"), Order2:=xlAscending

We take B5:D13 as Range, and we used Range.Sort method to sort this range.

Based on the value of C8 the range will be sorted, and the order for sorting will be in ascending order.

Based on the value of D8 the range will be sorted, and the order for sorting will be in ascending order.

End Sub

Finally, we end the Sub procedure.

After that, we Run the VBA code.

Then, you will see that multiple columns have been sorted in ascending order.

We have highlighted similar Prices and their Purchase Date in Yellow color. When two Prices are the same, sorting occurred based on the Purchase Date. Thus, you can sort multiple columns in ascending order using VBA in Excel.

Sort Multiple Columns of a Range Based on Multiple Columns in ascending order in Excel VBA

Read More:  Excel Macro: Sort Multiple Columns with Dynamic Range


4. VBA Macro to Sort Data by Just Double-Clicking on Header of Dataset

In the following video, you can see that when we double-click on the Product Name column, the column gets sorted in ascending order. In a similar way, when we double-click on the Price column, the column gets sorted in ascending order. Therefore, we are able to sort data by  Just Double-Clicking on the Header of the Dataset. Further, we will find out how to do so.

First of all, we will simply right-click on Sheet5.

Then, we will select View Code from the Context Menu.

This will bring out a Private Sub.

Right-clicking on Sheet and selecting view Code

Then, we will type the following code in the Private Sub.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim iRange As Range
Dim iCount As Integer
iCount = Range("B4:D13").Columns.Count
Cancel = False
If Target.Row = 4 And Target.Column <= iCount Then
Cancel = True
Set iRange = Range(Target.Address)
Range("B4:D13").Sort Key1:=iRange, Header:=xlYes
End If
End Sub

Code Breakdown

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

The event handler begins here.  It specifies that the code should be executed before the user double-clicks on a worksheet cell. The cell that was double-clicked on is the “Target” parameter, and the “Cancel” parameter is a Boolean variable that can be used to cancel the double-click event.

Dim iRange As Range
Dim iCount As Integer
iCount = Range("B4:D13").Columns.Count

We declare iRange as Range and iCount as Integer. “iCount” will be used to store the number of columns in the range “B4:D13“.

Cancel = False

This line defaults to “False,” indicating that the double-click event will not be canceled unless certain conditions are met.

If Target.Row = 4 And Target.Column <= iCount Then

The IF statement is used to check whether the cell that was double-clicked on is in the fourth row of the worksheet and within the range of columns “B4:D13“. The code will continue to execute if both conditions are met.

Cancel = True
Set iRange = Range(Target.Address)
Range("B4:D13").Sort Key1:=iRange, Header:=xlYes

The “Cancel” parameter is set to “True” in these lines, which cancels the double-click event. “iRange” is set to the range of the cell that was double-clicked on. The columns in the range “B4:D13” have been sorted by the value of the double-clicked cell. The “Key1” parameter specifies that the sort should be based on the value of “iRange,” and the “Header” parameter specifies that the first row of the range contains column headers.

End If
End Sub

This line ends the IF statement and the Sub procedure.

After running the code, when you will double-click on the header of the Product Name column, the column will be sorted. Also, when you will double-click on the header of the Price  column, the column will be sorted.

Read More: Excel VBA to Sort by Column Header Name


5. Sorting Column in Ascending Order Based on Cell Selection

In the following video, you can see that, we have selected a cell, here we select the cell that contains Battery, and then we run a code. Therefore, the VBA code sorts the columns in ascending order based on that selected cell in Excel. Now, we will show you how you can do a similar task.

First of all, we will type the following VBA code in Module6.

Sub Sort_Based_on_aCell()
Dim set_array As Variant
Dim my_rng As Range
Application.ScreenUpdating = False
On Error GoTo ifcellisnotselected
Set my_rng = Selection.Columns(1)
my_rng.Select
On Error GoTo 0
ActiveSheet.Sort.SortFields.Clear
my_rng.Sort Key1:=my_rng.Cells(1), Order1:=xlAscending, Header:=xlYes
Exit Subifcellisnotselected:
MsgBox "select a cell or range of cells before running this code.", 16, "You have selected no cells"
End Sub

Code Breakdown

Sub Sort_Based_on_aCell()

We take Sort_Based_on_aCell as the Sub procedure.

Dim set_array As Variant
Dim my_rng As Range

We declare set_array as Variant, and my_rng as Range.

Application.ScreenUpdating = False

Disables screen updating in the application to avoid flickering and improve code speed.

On Error GoTo ifcellisnotselected
If an error occurs, the code will proceed to the "ifcellisnotselected" label.
Set my_rng = Selection.Columns(1)

Makes the “my rng” variable the first column of the worksheet’s selected range.

my_rng.Select

This line uses the “my rng” variable to select the range.

On Error GoTo 0

This line deactivates the error handling procedure.

ActiveSheet.Sort.SortFields.Clear
This line removes any previously sorted fields.
my_rng.Sort Key1:=my_rng.Cells(1), Order1:=xlAscending, Header:=xlYes

Sorts the “my rng” range in ascending order by the first cell of the range and includes the header row.

Exit Sub

Exits the subroutine.

ifcellisnotselected:
MsgBox "select a cell or range of cells before running this code.", 16, "You have selected no cells"

This is the error handling method. If the user does not select any cells before running the code, a message box with the specified message will appear.

End Sub

This line ends the Sub procedure.

Then, we select the cell that contains the product Battery.

Finally, when we Run the VBA code, it will sort the columns in ascending order based on Battery in Excel.

Cell Sorted Based on a Selecetd Cell in ascending order using VBA in Excel


6. VBA Code to Sort a Predetermined Cell Range in Ascending Order

In the following video, you can see that we have a predetermined cell range in our code. Now, when we run the code, it will sort the columns of the dataset in ascending order based on the predetermined cell range. Now, we will describe how we can do the task.

First of all, we will type the following VBA code in Module7.

Sub Sorting_Based_on_predetermined_cellrange()
Dim my_range As Range
Application.ScreenUpdating = False
Set my_range = Range("B5:D13")
ActiveSheet.Sort.SortFields.Clear
my_range.Sort Key1:=my_range.Cells(1), _
Order1:=xlAscending, Header:=xlNo
End Sub

Code Breakdown

Sub Sorting_Based_on_predetermined_cellrange()

We take Sorting_Based_on_predetermined_cellrange as the Sub procedure.

Dim my_range As Range

We declare my_range as Range.

Application.ScreenUpdating = False

This line disables screen updating in the application to avoid flickering and improve code speed.

Set my_range = Range("B5:D13")

We assign Range(“B5:D13”) to my_range.

ActiveSheet.Sort.SortFields.Clear

This line removes all sort criteria from the active sheet.

my_range.Sort Key1:=my_range.Cells(1), Order1:=xlAscending, Header:=xlNo

This line sorts the cells in the variable “my range.”

It states that you should use the first cell in the range as primary key to in ascending order, with no header row.

End Sub

Ends the Sub procedure.

Finally, when we will run the code, it will sort the columns of the dataset based on the predetermined range.

Sorting a Predetermined Cell Range in Ascending Order


7. Insert Sorted Data in a Different Location with Excel VBA

In the following video, you can easily notice that we have sorted the dataset based on the Purchase Date, and we have inserted the sorted dataset in a different location. Now, we will describe how you can insert sorted data in a different location with Excel VBA.

In the beginning, we will type the following VBA code in Module8.

Sub Inserting_sorted_data_in_different_location()
Dim my_array() As Variant
my_array = Application.Transpose(ActiveSheet.Range("D5:D13"))
For i = LBound(my_array) To UBound(my_array)
For j = i + 1 To UBound(my_array)
If my_array(i) > my_array(j) Then
Store = my_array(j)
my_array(j) = my_array(i)
my_array(i) = Store
End If
Next j
Next i
For i = 1 To UBound(my_array)
For j = 1 To ActiveSheet.Range("B5:D13").Rows.Count
If my_array(i) = ActiveSheet.Range("B5:D13").Cells(j, 3) Then
ActiveSheet.Range("F4").Cells(i, 1) = ActiveSheet.Range("B5:D13").Cells(j, 1)
ActiveSheet.Range("F4").Cells(i, 2) = ActiveSheet.Range("B5:D13").Cells(j, 2)
ActiveSheet.Range("F4").Cells(i, 3) = ActiveSheet.Range("B5:D13").Cells(j, 3)
End If
Next j
Next i
End Sub

Code Breakdown

Sub Inserting_sorted_data_in_different_location()

We take Inserting_sorted_data_in_different_location as the Sub procedure.

Dim my_array() As Variant

We declare my_array as Variant.

my_array = Application.Transpose(ActiveSheet.Range("D5:D13"))

This line assigns the values of cells on the active sheet in the range “D5:D13” to the my_array, transposing it to a one-dimensional array.

For i = LBound(my_array) To UBound(my_array)
For j = i + 1 To UBound(my_array)
If my_array(i) > my_array(j) Then
Store = my_array(j)
my_array(j) = my_array(i)
my_array(i) = Store
End If
Next j
Next i

The For loops iterate through the array, comparing each value to the values that follow it. If the loop discovers a value greater than the value immediately following it, it will switch their positions.

For i = 1 To UBound(my_array)
For j = 1 To ActiveSheet.Range("B5:D13").Rows.Count
If my_array(i) = ActiveSheet.Range("B5:D13").Cells(j, 3) Then
ActiveSheet.Range("F4").Cells(i, 1) = ActiveSheet.Range("B5:D13").Cells(j, 1)
ActiveSheet.Range("F4").Cells(i, 2) = ActiveSheet.Range("B5:D13").Cells(j, 2)
ActiveSheet.Range("F4").Cells(i, 3) = ActiveSheet.Range("B5:D13").Cells(j, 3)
End If
Next j
Next i

These lines iterate over the sorted values in my array, looking for values that match the range “B5:D13” on the active sheet. If a match is found, this code copies the values in the corresponding row of the range to cells in the range “F4:H12,” beginning at the top and working down.

End Sub

This ends the Sub procedure.

Afterward, when we Run the code, the sorted dataset will be located in a new place.

Placing the Sorted Cells in Different Location


8. Sort Data by Cell Fill Color with Excel VBA

In the following video, you can see that the Purchase Price column has Red, Green, and Blue colored cells. Next, we sort the column in ascending order based on cell fill color using VBA in Excel. Further, we will show how we can do the task.

First of all, we will type the following VBA code in Module9.

Sub Sorting_by_Cell_Fill_Color()
ActiveWorkbook.Worksheets("Sheet10").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet10").Sort.SortFields.Add(Range("D5"), _
xlSortOnCellColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
ActiveWorkbook.Worksheets("Sheet10").Sort.SortFields.Add(Range("D5"), _
xlSortOnCellColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(0, 255, 0)
ActiveWorkbook.Worksheets("Sheet10").Sort.SortFields.Add(Range("D5"), _
xlSortOnCellColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
With ActiveWorkbook.Worksheets("Sheet10").Sort
.SetRange Range("B4").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Code Breakdown

Sub Sorting_by_Cell_Fill_Color()

We take Sorting_by_Cell_Fill_Color as the Sub procedure.

ActiveWorkbook.Worksheets("Sheet10").Sort.SortFields.Clear

This line clears the Sort field of Sheet10.

ActiveWorkbook.Worksheets("Sheet10").Sort.SortFields.Add(Range("D5"), _
xlSortOnCellColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
ActiveWorkbook.Worksheets("Sheet10").Sort.SortFields.Add(Range("D5"), _
xlSortOnCellColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(0, 255, 0)
ActiveWorkbook.Worksheets("Sheet10").Sort.SortFields.Add(Range("D5"), _
xlSortOnCellColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)

We used the .Add method three times to add the sort fields based on cell color.

Here, The xlSortOnCellColor argument specifies that the code should sort based on the cell fill color, and the xlAscending argument specifies that the code should sort in ascending order each time we added it.

We set the SortOnValue.Color property to the color of the first cells we want to sort, followed by cells of the next color, and so on.

.SetRange Range("B4").CurrentRegion

In this line, we used the SetRange method to define the range to be sorted, which is defined as the range beginning with cell B4 and encompassing all adjacent cells in the current region.

.Header = xlYes

We set the Header property to xlYes to indicate that the first row contains headers.

.MatchCase = False

We set the MatchCase property to False to indicate that the sorting should not be case sensitive, .

.Orientation = xlTopToBottom

Next, we set the Orientation property to xlTopToBottom to indicate that the sorting should be done from top to bottom.

.SortMethod = xlPinYin

We set the SortMethod property to xlPinYin to indicate that we should use the Chinese Phonetic Alphabet method for sorting.

.Apply
End With
End Sub

Then, we used the Apply method to actually sort the data on the worksheet by applying the sort settings.

Finally, we end the Sub procedure.

After that, when you will run the code, it will sort the column based on cell color. Here, it will sort Red-colored cells first, then Green colored cells, and then Blue colored cells.

Sorted Column based on Fill Color of Cell


9. Sort Data by Font  Color with Excel VBA

In the following video, you can see that the Purchase Price column has Red, Green, and Blue colored font. Next, we sort the column based on cell font color using VBA. Further, we will shows how we can do the task.

In the beginning, we will type the following VBA code in Module10.

Sub Sortingby_Font_Color()
ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add(Range("D5"), _
xlSortOnFontColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add(Range("D5"), _
xlSortOnFontColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(0, 255, 0)
ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add(Range("D5"), _
xlSortOnFontColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
With ActiveWorkbook.Worksheets("Sheet11").Sort
.SetRange Range("B4").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Code Breakdown

Sub Sortingby_Font_Color()

We take Sortingby_Font_Color as the Sub procedure.

ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Clear

This line removes all sort fields from Sheet11.

ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add(Range("D5"), _
xlSortOnFontColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add(Range("D5"), _
xlSortOnFontColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(0, 255, 0)
ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add(Range("D5"), _
xlSortOnFontColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)

These lines add a sort field to sort the data in ascending order based on the font color of cell D5.  We set the SortOnValue.Color property to RGB(255, 0, 0), indicating that it will sort cells with red font color first. Then, it will sort Green font color, and finally, it will sort Blue font color.

With ActiveWorkbook.Worksheets("Sheet11").Sort
.SetRange Range("B4").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

This statement restricts the cell range to the present location of cell B4 on Sheet11. It also controls the sort operation’s features, such as whether there is a header row, if the sort is case-sensitive, the sort’s orientation, and the sort technique. Finally, we used the sort procedure.

End Sub

Then, we end the subprocedure.

After that, when you will run the code, the column will get sorted based on font color. Here, Red-colored cells will be sorted first, then Green colored cells, and then Blue colored cells.

Sorted Column based on Font Color


How to Record a Macro to Sort in Excel VBA

In the following video, you can easily notice that we have sorted the Product Name column in ascending order using record macro. Now, we will describe how you can do so.

First of all, we will click on the record macro icon.

Then, a Record Macro dialog box will appear.

After that, we will set a Macro name, here we keep the default Macro name  Macro 1.

Then, click OK.

Therefore, it will create Macro 1.

After that, whatever we will do, it will store the code as a command in Macro 1.

Starting Record Macro

Now, we will sort the Product Name column.

To do so, we will select the Product Name column excluding its heading.

Then, we will go to the Home tab >> select Editing.

Then, from Sort & Filter >> select Sort A to Z.

Selecting Sort A to Z for Product Name column

At this moment, a Sort Warning will appear.

Then, we will select Continue with the current section>> click OK.

Select Continue with the current section from Sort warning

Therefore, you can see the sorted Product Name column.

After that, we will stop the Record Macro by cycling on the record macro icon.

Stopping Macro record

This will create the following code in Module12.

Now, we can use this VBA code to sort the Product Name again.

Sub Macro1()
Range("B5:B13").Select
ActiveWorkbook.Worksheets("Sheet12").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet12").Sort.SortFields.Add2 Key:=Range("B5:B13" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet12").Sort
.SetRange Range("B5:D13")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Finally, after running the code, you can see it has sorted the Product Name column in ascending order.

Sorted Product Name Column using Record Macro


What to Do If VBA Sort Is not Working in Excel?

In many cases, you may face that VBA sort is not working. This might happen when you are proving wrong cell reference in Rane.Sort method.

While using Range.Sort method for VBA sorting, make sure to write the syntax properly. You must provide the Cell range carefully. If you provide the wrong cell range, then the VBA sort will not execute.

For a better understanding, see the following video. Here, we want to sort the Product Name in ascending order. The correct range with a header for sorting would be Range(B4:B14). However, since we typed the Range as Range(“B1:B14”) which is wrong, the VBA sort will not work, and an error will pop up.

Next, when we typed the correct range carefully which is Range(B4:B14), the VBA sort work properly.

Therefore, you must double-check before providing the range. We hope your VBA sort will work properly now.


Frequently Asked Questions

Here, we will give you answers to some of the frequently asked questions about VBA sort. We hope this will be helpful for you.

  • How to Sort Column Descending?

In the following video, you can see that we have a single column that is showing Salary of Numerous Employees. Now, we want to sort the salaries in descending order. You can easily notice that we have used a simple VBA code for this.

First of all, we typed the following VBA code in Module12.

Sub Sort_Column_Descending()
Range("B4:B14").Sort Key1:=Range("B4"), _
Order1:=xlDescending, Header:=xlYes
End Sub

Code Breakdown

Sub Sort_Column_Descending()

We take Sort_Column_WiDescending as the Sub procedure.

Range("B4:B14").Sort Key1:=Range("B4"), Order1:=xlDescending, Header:=xlYes

We take B4:B14 as Range, and we used Range.Sort method to sort this range.

This will sort the range based on the value of B4.

The order for sorting will be an descending order.

xlYes defines that the column has a header.

End Sub

Finally, we end the Sub procedure.

After that, when we run the VBA code, it will sort the column in descending order.

  • How to Sort Column Alphabetically?

We will show you how you can sort a column alphabetically using the following dataset. Here, we want to sort the Product Name column alphabetically from A to Z.

To do so, we will select the entire Product Name column excluding the header.

Then, we will go to the Home tab >> select Sort & Filter >> select Sort A to Z.

Selecting Sort A to Z for Alphabetical Sorting

Then, a Sort Warning will appear.

We will select Continue with the current selection >> click OK.

selecting Continue with the current selection

Therefore, you can see the alphabetically sorted column.alphabetically sorted column

  • How to Fix When Excel Sort Is Not Working

If your Excel sort is not working, you can follow the two solutions described below to get rid of the situation.

Solution 1: Deleting Same Sheet’s Reference from Formula If Sort is Not Working

Solution 2: Using Date Format Or Text to Column Wizard for Date Sorting


Key Takeaways from the Article

This article is useful for readers who are looking for Excel VBA to sort a column ascending. Let’s take a glance at what we learned from this article:

  • You have learned about Sort method.
  • 9 effective examples for Excel VBA to sort column ascending.
  • Recording a Macro for VBA sorting.
  • Fix the problem when VBA sort is not working.
  • Along with that, we provided you with the answer to some frequently asked questions.

Conclusion

This article provides 9 examples of Excel VBA sort column ascending. We try to cover all the aspects of VBA sorting. We hope this article will be immensely helpful for you. If you have any questions or suggestions, please let us know in the comment section.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo