In VBA, sorting data is a common task, but it can be frustrating when the sort function doesn’t work as expected. This article will discuss the different causes of the “VBA Sort Not Working” issue and possible solutions to address it. By resolving these issues hopefully you can sort your data correctly in Excel.
Download Practice Workbook
You can download this workbook from here.
How to Launch VBA Editor in Excel
In this section, we are going to create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.
- First, we go to the Developer
- Then we will select Visual Basic.
A new window will pop up. After that, we are going to follow these steps,
- First, we will select Insert,
- Then we are going to select Module.
- A new Module will be created.
Causes of Errors While Sorting in Excel VBA
There can be several reasons why this issue occurs. These include, but are not limited to:
- Incorrect range or object selection
- Invalid sort parameters
- Hidden or filtered data
- Incompatible data types or formats, or
- VBA code errors.
VBA Sort Not Working in Excel : 4 Possible Solutions
First and foremost, to troubleshoot this particular issue and VBA in general
- Verify the correct selection of the sorting range
- Check the sorting parameters’ validity
- Unhide any hidden data
- Remove filters
- Ensure compatibility of data types and formats
- Debug the VBA code to eliminate syntax or logic errors.
In addition to many fundamental problems like syntax errors that people generally encounter while running VBA codes, there are many reports of general errors that contribute particularly to the sorting issue. We are opting for the discussion of those problems in this section.
Here we used two datasets for showing the example. The first one is for example 1 and 3.
1. Activate Worksheet When Sort is Implemented
Suppose you have an unsorted dataset like this below
- Now you have given the code below for sorting.
This code is not done for an active worksheet with proper sub routine. To solve this problem you have to write code for activating the current worksheet.
- Now it looks for active window, But the previous code does not have active windows.
- Now the corrected code is given below where the sheet is activated properly.
- You can copy the code from here.
Sub test()
sortSelectionByDate ActiveSheet, 5, 12
End Sub
Public Sub sortSelectionByDate(ByRef worksheet As worksheet, ByVal frmRow As Integer, ByVal Row As Integer)
'debug
Row = Row - 1
worksheet.Select
worksheet.Rows(frmRow & ":" & Row).Select
With Selection
.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub- Now, to Run the code click on the Run button or press F5.
🔎 Code Explanation
Sub test()
sortSelectionByDate ActiveSheet, 5, 12
End Sub- The first line defines a subroutine called “sub test”, which does not take any input arguments.
- The second line calls a subroutine called “sortSelectionByDate” and passes three arguments: the active worksheet, the starting row (5), and the ending row (12).
Public Sub sortSelectionByDate(ByRef worksheet As Worksheet, ByVal frmRow As Integer, ByVal Row As Integer)
'debug
Row = Row - 1
worksheet.Select- The “sortSelectionByDate” subroutine is a public subroutine that takes three arguments: a worksheet reference (passed by reference with the “ByRef” keyword), a starting row number, and an ending row number.
- The fourth line of the subroutine subtracts 1 from the ending row number. This is likely done to account for the fact that row numbers in VBA start at 1, while the “Row” argument may represent the last row number in a selection.
- The fifth line selects the worksheet that was passed as an argument.
worksheet.Rows(frmRow & ":" & Row).Select
With Selection
.sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub- The sixth line selects a range of rows in the worksheet based on the starting and ending row numbers passed as arguments.
- The seventh line starts a “With” block that applies a sorting operation to the range of selected cells.
- The eighth line specifies that the sorting operation should be based on the cell in column B, row 4 (“Range(“B4″)”).
- The ninth line specifies that the sorting should be done in ascending order (“Order1:=xlAscending”).
- The tenth line indicates that Excel should attempt to guess whether or not the selected range includes headers (“Header:=xlGuess”).
- The eleventh line specifies that the “OrderCustom” parameter should be set to 1.
- The twelfth line indicates that case sensitivity should not be used during the sorting operation (“MatchCase:=False”).
- The thirteenth line specifies that the sorting operation should proceed from top to bottom (“Orientation:=xlTopToBottom”).
2. When VBA Sort Is Not Working Select Proper Argument
Here if you provide a wrong range in your code you will get 1004, Application Defined or Object Defined Error.
- The erroneous code is given below.
- If you select the correct Range, it will solve the problem. Yo can find the corrected code bellow.
- You can copy the code from here.
Public Sub Sort() 'Sort
Dim x As Long
ActiveWorkbook.Worksheets("Project").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Project").Sort.SortFields.Add _
Key:=Range("B5:B" & Cells(5, 2).End(xlDown).Row), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Project").Sort
.SetRange Range("B5:B" & Cells(5, 2).End(xlDown).Row)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub- Now, to Run the code click on the Run button or press F5.
🔎 Code Explanation
Public Sub Sort() 'Sort
Dim x As Long- This code defines a VBA sub procedure called “Sort”, which clears any existing sort criteria in the “Project” worksheet and then sorts a range of cells in ascending order based on the values in column B. We declared the variable “x” as a Long integer, but did not use it in the code.
ActiveWorkbook.Worksheets("Project").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Project").Sort.SortFields.Add _
Key:=Range("B5:B" & Cells(5, 2).End(xlDown).Row), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers- This line adds a new sort field to the “Project” worksheet. The key is set to the range from cell B5
to the last non-empty cell in column B. The “SortOn” parameter specifies that the sort should be
based on values, not formatting or formulas. The “Order” parameter specifies that the sort should
be in ascending order. The “DataOption” parameter specifies that text values should be sorted as
numbers.
With ActiveWorkbook.Worksheets("Project").Sort
.SetRange Range("B5:B" & Cells(5, 2).End(xlDown).Row)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub- This block of code sets the range to be sorted to the same range as in the previous line. It set the “Header” parameter to “xlGuess”, which means that Excel should determine whether the first row of the range contains column headings or not. Then, it set “MatchCase” parameter to “False”, which means that the sort should be case-insensitive. We set the “Orientation” parameter to “xlTopToBottom”, which means that the sort should be applied from the top of the range to the bottom. Finally, we set “SortMethod” parameter to “xlPinYin”, which means that Chinese characters should be sorted based on their phonetic pronunciation. Finally, we called the “Apply” method to apply the sort criteria to the range.
3. Use Auto-Filter Correctly When VBA Sort Is Not Working
Here is the sort of general error that people often do.
- But if you face the problem write down the code below.
- You can copy the code from here.
Sub filterCorrection()
Set targetworksheet = ThisWorkbook.Worksheets("Correct auto filter")
targetworksheet.Activate
lastCol = ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Column
'Rows("1:1").Select
Range("B4").Select 'select cell B4
Range(Selection, Selection.End(xlToRight)).Select 'extend selection to last column
Selection.AutoFilter
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
End Sub
Sub sortlists(i As Long)
targetworksheet.AutoFilter.sort.SortFields.Clear
targetworksheet.AutoFilter.sort.SortFields.Add Key:=Cells(1, i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Correct auto filter").AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub- Now, to Run the code click on the Run button or press F5.
🔎 Code Explanation
Sub filter()
Set targetws = ThisWorkbook.Worksheets("Validation2")
targetws.Activate
lastColumn = ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Column
'Rows("1:1").Select
Range("B4").Select 'select cell B4
Range(Selection, Selection.End(xlToRight)).Select 'extend selection to last column
Selection.AutoFilter
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
End SubThe first subroutine, named “filter,” performs the following actions:
- Sets the target worksheet to “Validation2” within the current workbook.
- Activates the target worksheet.
- Determines the last column in row 4 of the active sheet.
- Selects cell B4 on the active sheet.
- Extends the selection to the last column in row 4.
- Applies an auto-filter to the selection.
- Freezes the top row of the active window.
Sub sortlists(i As Long)
' it get a number to specify which value it use for sorting, most of the code from macro recording
targetworksheet.AutoFilter.sort.SortFields.Clear
targetworksheet.AutoFilter.sort.SortFields.Add Key:=Cells(1, i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Correct auto filter").AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub- Clears any existing sort fields in the auto-filter of the target worksheet.
- Adds a new sort field to the auto-filter using the specified column number (i) as the key.
- Specifies that you should sort values in ascending order.
- Specifies that the sort should use normal sorting options.
- Configures the auto-filter sort options, including header row inclusion, case sensitivity, orientation, and sorting method.
- Applies the sort to the auto-filter.
4. Apply Alternate Ways to Sort Arrays
In this section, we will provide an error correction for sorting an array. Above the picture a possible fault provide.
- To correct the code below, We are going to give the code below.
- You can copy the code from here.
Sub SortColors()
Dim colors() As Variant
colors= Array("red", "orange", "yellow", "green", "blue", "purple", "pink", "brown", "gray", "black", "white")
' Sort the colors array alphabetically using bubble sort
Dim i As Long, j As Long
Dim temp As String
For i = LBound(colors) To UBound(colors) - 1
For j = LBound(colors) To UBound(colors) - 1 - i
If UCase(colors(j)) > UCase(colors(j + 1)) Then
temp = colors(j)
colors(j) = colors(j + 1)
colors(j + 1) = temp
End If
Next j
Next i
' Print the sorted colors to the Immediate window
Dim k As Long
For k = LBound(colors) To UBound(colors)
Debug.Print colors(k)
Next k
End Sub- Now, to Run the code click on the Run button or press F5.
🔎 Code Explanation
Sub SortColors()
Dim colors() As Variant
colors = Array("red", "orange", "yellow", "green", "blue", "purple", "pink", "brown", "gray", "black", "white")These lines declares and initializes the myArray variable as a variant array containing a list of color names.
' Sort the colors array alphabetically using bubble sort
Dim i As Long, j As Long
Dim temp As String
For i = LBound(colors) To UBound(colors) - 1
For j = LBound(colors) To UBound(colors) - 1 - i
If UCase(colors(j)) > UCase(colors(j + 1)) Then
temp = colors(j)
colors(j) = colors(j + 1)
colors(j + 1) = temp
End If
Next j
Next iThis code implements a bubble sort algorithm to sort the myArray array in ascending order.
- The outer loop (controlled by the i variable) loops through each element of the array, except for the last one (which you have already sorted at that point).
- The inner loop (controlled by the j variable) loops through each pair of adjacent elements in the array and compares them.
- If the first element is greater than the second element (in alphabetical order), this code will swap the two elements.
- Here, we used the temp variable to hold the value of the first element temporarily during the swap.
' Print the sorted colors to the Immediate window
Dim k As Long
For k = LBound(colors) To UBound(colors)
Debug.Print colors(k)
Next k
End SubThis code uses a For loop to loop through each element of the sorted myArray array and prints it to the Immediate window using the Debug.Print statement.
How to Sort a Column Using Excel VBA
Here is an example of single-column sorting. So we have the unsorted dataset.
- Now we are going to implement the following code in the module.
- You can copy the code from here.
Sub AgeColumns()
Range("D5:D11").Sort Key1:=Range("D5"), _
Order1:=xlAscending, Header:=xlNo
End Sub- Finally, Run the code by clicking on the Run button or pressing F5.
🔎 Code Explanation
Sub AgeColumns()- At first, we declared the VBA subroutine called “AgeColumns”.
Range("D5:D11").Sort Key1:=Range("D5"), _- This selects the range of cells from D5 to D11 using the Range function and applies the Sort method to it. Key1 parameter specifies the column to sort by, which is the range of cells from D5 to D11 in this case.
Order1:=xlAscending, Header:=xlNo- This specifies that the data should be sorted in ascending order and that there is no header row in the selected range. The xlAscending is a constant that represents the ascending order while xlNo is a constant that represents no header row.
End Sub- This marks the end of the subroutine.
How to Sort Rows Based on a Column Using VBA in Excel
Here we have given an overview of the entire table setting using VBA code. In this article, we are going to demonstrate how to sort entire rows using VBA. Suppose we have an unsorted data table
- Now you can write the code below in the module
- You can copy the code from here.
Sub SortByAge()
Dim LastRow As Long
'Get the last row number
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
'Sort the data based on Age column
Range("B4:D" & LastRow).Sort Key1:=Range("D4:D" & LastRow), _
Order1:=xlAscending, Header:=xlYes
End Sub- Now, to Run the code click on the Run button or press F5 to find out the sorted table.
🔎 Code Explanation
Sub SortByAge()- This line declares a subroutine named SortByAge.
Dim LastRow As Long- This line declares a variable named LastRow as a long integer.
'Get the last row number
LastRow = Cells(Rows.Count, "B").End(xlUp).Row - This line sets the value of LastRow to the last row number in column “B” that has data. We have accomplished by starting at the bottom of the worksheet (Rows.Count), looking in column “B” (“B”), and then moving up until the last cell with data is found (End(xlUp)).
'Sort the data based on Age column
Range("B4:D" & LastRow).Sort Key1:=Range("D4:D" & LastRow), _
Order1:=xlAscending, Header:=xlYes- This line sorts the data in columns “B” through “D” based on the values in column “D”. The Key1 parameter specifies the range of cells to sort by (Range(“D4:D” & LastRow)). The Order1 parameter specifies the sorting order (xlAscending), which means to sort from smallest to largest). The Header parameter indicates that the data has a header row (xlYes).
End SubHow to Sort Multiple Columns Using SORT Function in Excel VBA
Here we are going to sort two columns using VBA. We have our unsorted dataset.
- Now we are going to write the simple code in the module.
- You can copy the code from here.
Sub SortNameAndAgeColumns()
'Sort the Name and Age columns in ascending order
Range("B5:B11").Sort Key1:=Range("B5"), _
Order1:=xlAscending, Header:=xlNo
Range("D5:D11").Sort Key1:=Range("D5"), _
Order1:=xlAscending, Header:=xlNo
End Sub- Now, to Run the code click on the Run button or press F5.
🔎 Code Explanation
Sub SortNameAndAgeColumns()- This line starts the definition of a VBA macro named “SortNameAndAgeColumns”.
'Sort the Name and Age columns in ascending order
Range("B5:B11").Sort Key1:=Range("B5"), _
Order1:=xlAscending, Header:=xlNo- This line selects the range of cells B5 through B11 using the Range method and sorts them in ascending order based on the values in cell B5 using the Sort method. The Key1 parameter specifies the range to sort by (in this case, B5). The Order1 parameter specifies the sort order (ascending in this case). The Header parameter specifies whether the range has headers (not in this case).
Range("D5:D11").Sort Key1:=Range("D5"), _
Order1:=xlAscending, Header:=xlNo- This line selects the range of cells D5 through D11 using the Range method and sorts them in ascending order based on the values in cell D5 using the Sort method. The Key1 parameter specifies the range to sort by (in this case, D5). The Order1 parameter specifies the sort order (ascending in this case). The Header parameter specifies whether the range has headers (not in this case).
End Sub- This line ends the definition of the VBA macro.
FAQs
1. Why isn’t my VBA sort working?
If you give incorrect criteria, data validation, and formatting you will not have proper sorting. Invalid data hinders the sorting process.
2. Why is my VBA sort only sorting part of the data?
If you select the range in the wrong way, you can not obtain the desired range to be sorted.If there are hidden rows and then you will find problems while sorting your selected range.
3. Why is my VBA sort not working in Excel 2016 or later versions?
If your VBA sort is not working in Excel 2016 or later versions, it could be due to a change in the way Excel handles the sorting process. In Excel 2016 and later versions, the default behavior for sorting has changed to include header rows by default. To resolve this issue, you can add the Header:=xlNo argument to the SortFields.Add a method to exclude the header row from the sort.
Another reason why your VBA sort may not be working in Excel 2016 or later versions is that there may be changes to the syntax or arguments for the SortFields.Add method. Check the documentation for the specific version of Excel you are using to ensure that you are using the correct syntax and arguments for the SortFields.Add method.
Things To Remember
- Make sure the range you are sorting is valid and exists on the active worksheet.
- Check that the sort order and criteria are correct.
- Ensure that the range you are sorting does not contain merged cells, as VBA does not support sorting merged cells.
- Check if the range you are sorting includes hidden rows or columns, as these may cause unexpected results.
- Verify that the data in the range is in the correct format and does not contain any errors or invalid entries.
- If you are using a named range, ensure that the named range refers to a valid range on the active worksheet.
- Check if there are any filters applied to the range you are sorting, as filters can affect the sorting order and criteria.
- Ensure that the active worksheet is not protected, as this may prevent sorting.
- If you are using a macro or code to sort the data, ensure that the code is free of syntax errors and that it is executing properly.
- Try restarting Excel or resetting the VBA environment if all else fails.
Conclusion
If you are experiencing issues with the VBA sort function not working, there could be several reasons for this. One possible cause is incorrect syntax in the sorting statement, such as using the wrong property or method names or failing to properly reference the sorting range. Another common cause of VBA sort failure is incorrect range selection, where you may not have included all the necessary rows or columns which range you want to sort. Other factors that may cause VBA sort to fail include missing or incorrect references, conflicts with other code in the macro, or issues with the data type or formatting of the sorting column.
To troubleshoot the issue, we recommend to review the code for any syntax errors, double-check the selected range, ensure all necessary references are available, and confirm the data type and formatting of the sorting column. By addressing these potential issues, it may be possible to resolve the VBA sort problem and successfully sort the desired data.
