How to Sort Smallest to Largest with VBA Autofilter in Excel: 3 Suitable Ways

Method 1 – Sort Smallest to Largest Using the Ascending Order in VBA Autofilter

Step 1:

Open a Module. 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. From that window, we will insert a module for applying our VBA code. Go to Insert → Module.

Step 2:

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

Run the VBA code. 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.

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

Step 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

Run the VBA code. Go to Run → Run Sub/UserForm.

Step 2:

After running the VBA code, you can sort our data according to the sales representatives’ names and order products from smallest to largest, as shown in the screenshot below.

Method 3 – Apply VBA Autofilter to Sort Smallest to Largest with Worksheet Name

Step 1:

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

Run the VBA code. Go to Run → Run Sub/UserForm.

Step 2:

After running the VBA code, you will be able to sort the data from smallest to largest according to the worksheet name given in the screenshot below.