While working with large Microsoft Excel, sometimes we need to sort our data from smallest to largest. We can easily do that by using the VBA Macros. Applying a VBA code to sort from smallest to largest is an easy task. Today, in this article, we’ll learn 3 quick and suitable ways to VBA auto-filter sort smallest to largest in Excel effectively with appropriate illustrations.
Sort Smallest to Largest VBA Autofilter in Excel (Quick View)
Sub Autofilter_Sort_Smallest_to_Largest()
With ActiveSheet.Sort
.SortFields.Add Key:=Range("D4"), Order:=xlAscending
.SetRange Range("B4:E16")
.Header = xlYes
.Apply
End With
End Sub
How to Sort Smallest to Largest with VBA Autofilter in Excel: 3 Suitable Ways
Let’s say, we have a dataset that contains information about several sales representatives of the Armani group. The Names of the sales representatives, the type of Products, and the Ordered and Delivered products by the sales representatives are given in columns B, C, D, and E respectively. From our dataset, we will sort our data from smallest to largest using VBA code. Here’s an overview of the dataset for today’s task.
1. Sort Smallest to Largest Using the Ascending Order in VBA Autofilter
Now I’ll show how to sort from smallest to largest in ascending order by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will sort our data according to the Ordered products in ascending order. Let’s follow the instructions below to sort data from smallest to largest!
Step 1:
- First of all, open a Module, to do that, firstly, from your Developer tab, go to, Developer → Visual Basic.
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Sort Data Smallest to Largest will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to, Insert → Module.
Step 2:
- Hence, the Sort Data Smallest to Largest module pops up. In the Sort Data Smallest to Largest module, write down the below VBA code:
Sub Autofilter_Sort_Smallest_to_Largest()
With ActiveSheet.Sort
.SortFields.Add Key:=Range("D4"), Order:=xlAscending
.SetRange Range("B4:E16")
.Header = xlYes
.Apply
End With
End Sub
- Hence, run the VBA code. To do that, go to, Run → Run Sub/UserForm.
- After running the VBA code, you will be able to sort data from smallest to largest which has been given in the below screenshot.
2. Use Multiple Columns to Sort Smallest to Largest with Header in Excel VBA
In this method, we will sort multiple columns with the header from smallest to largest. From our dataset, we will sort our data according to the Names of the sales representatives and Ordered products from smallest to largest. Let’s follow the instructions below to sort multiple columns with the header from smallest to largest!
Step 1:
- First, according to method 1, insert a new module and type the below VBA code to change the font color. The VBA code is,
Sub Sort_Multiple_Columns()
With ActiveSheet.Sort
.SortFields.Add Key:=Range("B4"), Order:=xlAscending
.SortFields.Add Key:=Range("D4"), Order:=xlAscending
.SetRange Range("B4:E16")
.Header = xlYes
.Apply
End With
End Sub
- Further, run the VBA code. To do that, go to, Run → Run Sub/UserForm.
Step 2:
- After running the VBA code, you will be able to sort our data according to the Name of the sales representatives and Ordered products from smallest to largest which has been given in the below screenshot.
3. Apply VBA Autofilter to Sort Smallest to Largest with Worksheet Name
Last but not least, you can also use your data to sort from smallest to largest according to the worksheet name using the VBA code in Excel. From our dataset, we will sort data from the smallest to largest of the Delivered products. This is an easy task and time-saving also. Let’s follow the instructions below to sort our data from smallest to largest according to the worksheet name!
Step 1:
- First, insert a new module according to method 1 and type the below VBA code to change the font color. The VBA code is,
Sub Sort_Smallest_to_Largest()
Rows("4:16").Select
ActiveWorkbook.Worksheets("Smallest to Largest").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Smallest to Largest").Sort.SortFields.Add Key:=Range("E4:E16") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Smallest to Largest").Sort
.SetRange Range("B4:E16")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
- Further, run the VBA code. To do that, go to, Run → Run Sub/UserForm.
Step 2:
- After running the VBA code, you will be able to sort data from smallest to largest according to the worksheet name which has been given in the below screenshot.
Things to Remember
👉 You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.
👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to, File → Option → Customize Ribbon.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to sort smallest to largest with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.