VBA Autofilter: Sort Smallest to Largest (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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

vba autofilter sort smallest to largest


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.

vba autofilter sort smallest to largest


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.

Sort Smallest to Largest Using the Ascending Order in VBA Autofilter

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.

Sort Smallest to Largest Using the Ascending Order in VBA Autofilter

  • After running the VBA code, you will be able to sort data from smallest to largest which has been given in the below screenshot.

Sort Smallest to Largest Using the Ascending Order in VBA Autofilter


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

Use Multiple Columns to Sort Smallest to Largest with Header in Excel VBA

  • 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.

Use Multiple Columns to Sort Smallest to Largest with Header in Excel VBA


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

Apply VBA Autofilter to Sort Smallest to Largest with Worksheet Name

  • 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.

Apply VBA Autofilter to Sort Smallest to Largest with Worksheet Name


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.


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.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo