Welcome to Excel Avon
Sort Data on Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
Today’s article is about Sort Data on Excel using VBA, today we are going to understand how to Sort data on Excel VBA, Excel already has a couple of ways to sort data quickly. You can easily sort data set by using the sort icons in the ribbon or the Sort dialog box. Knowing how to sort data on excel using VBA can be helpful when included as a part of your code.
Sorting a range in VBA is done by the Range.sort method. It is a property of the range method with which a user can sort a range in order. The arguments for Sort Data on Excel using VBA: Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3. All the arguments for this function are optional.
We created the data to be sorted which included the name, date of birth, and their state.
Sort Data on Excel using VBA
We have to go like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
Once we insert the module, we will write a subroutine to sort the data.
Sub SortData() End Sub
And then Define variable for Sortfield and Worksheet, set worksheet as activesheet
Sub SortData() Dim sfield as SortField Dim WS as Worksheet set WS = Activesheet End Sub
Now we will clear the previous sfield
Sub SortData() Dim sfield as SortField Dim WS as Worksheet set WS = Activesheet Ws.sort.SortField.Clear End Sub
After clearing the previous sort field, we will add a new s field, since we’re sorting the data by “name”, our key 1 argument will be the column range (“A1”).
Sub SortData()
Dim sfield as SortField
Dim WS as Worksheet
set WS = Activesheet
Ws.sort.SortField.Clear
Set sField = WS.Sort.SortFields.Add(WS.Range("A1"))
End Sub
How will the order of the sort field be Ascending / Descending, we have selected Ascending because we want data in A To z order.
Sub SortData()
Dim sfield as SortField
Dim WS as Worksheet
set WS = Activesheet
Ws.sort.SortField.Clear
Set sField = WS.Sort.SortFields.Add(WS.Range("A1"))
sfield.Order = xlAscending
End Sub
We want to sort sortfield as SortOnValues
Sub SortData()
Dim sfield as SortField
Dim WS as Worksheet
set WS = Activesheet
Ws.sort.SortField.Clear
Set sField = WS.Sort.SortFields.Add(WS.Range("A1"))
sfield.Order = xlAscending
sField.SortOn = xlSortOnValues
End Sub
We will use more functions of sort worksheet, here we use are using [xlPinYin] SortMethod and Range of data to sort [ ws.Range(“A1:C73“)].
Sub SortData()
Dim sfield as SortField
Dim WS as Worksheet
set WS = Activesheet
Ws.sort.SortField.Clear
Set sField = WS.Sort.SortFields.Add(WS.Range("A1"))
sfield.Order = xlAscending
sField.SortOn = xlSortOnValues
With WS.Sort
.SortMethod = xlPinYin
.SetRange WS.Range("A1:C73")
End Sub
If there is header in our data, then we will select xlYes for header. And end the code to (.apply)
Sub SortData()
Dim sfield as SortField
Dim WS as Worksheet
set WS = Activesheet
Ws.sort.SortField.Clear
Set sField = WS.Sort.SortFields.Add(WS.Range("A1"))
sfield.Order = xlAscending
sField.SortOn = xlSortOnValues
With WS.Sort
.SortMethod = xlPinYin
.SetRange WS.Range("A1:C73")
.Header = xlYes
.Apply
End With
End Sub
We’ll go into the worksheet, after written code Run the Macros
We wrote code for name column (A1) so name column is in A to Z.
Now we will go back to VBE and then put the name column in descending order, we need to select Descending in the sfield.Order
Sub SortData()
Dim sfield as SortField
Dim WS as Worksheet
set WS = Activesheet
Ws.sort.SortField.Clear
Set sField = WS.Sort.SortFields.Add(WS.Range("A1"))
sfield.Order = xldescending
sField.SortOn = xlSortOnValues
With WS.Sort
.SortMethod = xlPinYin
.SetRange WS.Range("A1:C73")
.Header = xlYes
.Apply
End With
End Sub
We’ll go into the worksheet, after written code Run the Macros.
After Click Run Button, we can see the Descending (Z to A)
Now we will assign a button to run the macros, you can assign buttons by going to the Insert tab.
After assigning the button we will go to VBE and write the code for Date of Birth (B1) column, we have already created the code so we will just write B1 in the key parameter.
Sub SortData()
Dim sfield as SortField
Dim WS as Worksheet
set WS = Activesheet
Ws.sort.SortField.Clear
Set sField = WS.Sort.SortFields.Add(WS.Range("B1"))
sfield.Order = xldescending
sField.SortOn = xlSortOnValues
With WS.Sort
.SortMethod = xlPinYin
.SetRange WS.Range("A1:C73")
.Header = xlYes
.Apply
End With
End Sub
As we have changed only key parameter and sort order is descending, go to active sheet and click on assign button. As you can see, we have arranged the date of birth column in descending order in which older people will come later and younger people first.
Now we will do two columns together in sort order, first back go to VBE, then copy and paste the formula that was written just below it and use the same column’s key as parameter in the column in which sort order is to be done. We will sort by state C1 and by state we will sort by nameA1.
Sub SortData()
Dim sfield as SortField
Dim WS as Worksheet
set WS = Activesheet
Ws.sort.SortField.Clear
Set sField = WS.Sort.SortFields.Add(WS.Range("C1"))
sfield.Order = xldescending
sField.SortOn = xlSortOnValues
Set sField = WS.Sort.SortFields.Add(WS.Range("A1"))
sfield.Order = xldescending
sField.SortOn = xlSortOnValues
With WS.Sort
.SortMethod = xlPinYin
.SetRange WS.Range("A1:C73")
.Header = xlYes
.Apply
End With
End Sub
After writing the code we will go to the worksheet and click on the assigned button, you can see that the State and Name columns are in descending order. First there was the descending order of the state in which NY came first, then the descending order was done by the name of all the people of NY.
Let’s go back to Visual Basic, both codes will run in ascending order.
Sub SortData()
Dim sfield as SortField
Dim WS as Worksheet
set WS = Activesheet
Ws.sort.SortField.Clear
Set sField = WS.Sort.SortFields.Add(WS.Range("C1"))
sfield.Order = xlAscending
sField.SortOn = xlSortOnValues
Set sField = WS.Sort.SortFields.Add(WS.Range("A1"))
sfield.Order = xlAscending
sField.SortOn = xlSortOnValues
With WS.Sort
.SortMethod = xlPinYin
.SetRange WS.Range("A1:C73")
.Header = xlYes
.Apply
End With
End Sub
After doing the sort Data order in ascending we will go to the worksheet, click on the assign button to run the macros. CA came first in the ascending order of the state, then put all the people in CA in ascending order.
So, I hope you have understood How to Sort Data on Excel using VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to Sort Data on Excel using VBA
