VBA Autofilter: Sort Smallest to Largest (3 Methods)

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

vba autofilter sort smallest to largest


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.

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

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

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

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

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

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

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

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

Apply VBA Autofilter to Sort Smallest to Largest with Worksheet Name

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

Apply VBA Autofilter to Sort Smallest to Largest with Worksheet Name

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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo