Md. Abdur Rahim Rasel
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 smallest to largest is an easy task. Today, in this article, we’ll learn three quick and suitable ways how to VBA autofilter 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
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Suitable Ways to Sort Smallest to Largest with VBA Autofilter in Excel
Let’s say, we have a dataset that contains information about several sales representatives of the Armani group. The Name 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 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 of the 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
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 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.
Read More: VBA to AutoFilter with Multiple Criteria on Same Field in Excel (4 Methods)
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 Name of the sales representatives, and ordered the 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 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 the products from smallest to largest which has been given in the below screenshot.
Read More: [Fix]: AutoFilter Method of Range Class Failed (5 Solutions)
3. Apply VBA Autofilter to Sort Smallest to Largest with Worksheet Name
Last but not the 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 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.
Read More: How to Autofilter and Copy Visible Rows with Excel VBA
Things to Remember
👉 You can pop up 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
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.
