In this chapter you will learn how to use Sort and Filter in VBA. Topics will be covered Sort, Sort by multiple columns, Filter, Filter by two columns, Filter by two Criteria and Filter by Color.
Sort
To sort the Sort property of range is used. In the Sort syntax we have to give Key, Order as xlAscending or xlDescending and header as xlYes, xlNo or xlGuess.
In the below given data we have to sort our data by Revenue in descending order.
Sub Sort_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
sh.UsedRange.Sort key1:=sh.Range("E1"), order1:=xlDescending, Header:=xlYes
End Sub
After executing this code data will look like below image.
Sort by multiple columns
Sub Sort_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
sh.UsedRange.Sort key1:=sh.Range("E1"), order1:=xlDescending, _
key2:=sh.Range("C1"), order2:=xlAscending, Header:=xlYes
End Sub
Filter
Filter is very useful functionality in excel. To use filter by using VBA we have to use AutoFilter property of the range. In the syntax of Auto filter we have to give the Field as number of column (that is to be filtered) and criteria1 as filter criteria.
In the below given data we have to filter the data of Supervisor-2 data.
Sub Filter_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
sh.UsedRange.AutoFilter 3, "Supervisor-2"
End Sub
After executing this code data will look like below image.
Filter by two columns
Let’s say we have to filter the data by two columns, where Supervisor Name is Supervisor-2 and Sales is less than 10.
Sub Filter_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
sh.UsedRange.AutoFilter 3, "Supervisor-2"
sh.UsedRange.AutoFilter 4, "<10"
End Sub
After executing this code data will look like below image.
Filter by two Criteria
Let’s say we have to filter the EMP Name is EMP-1 and EMP-2
Sub Filter_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
sh.UsedRange.AutoFilter 2, "EMP-1", xlOr, "EMP-2"
End Sub
After executing this code data will look like below image.
Filter by Color
To filter by color we have to give the criteria as color.
Sub Filter_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
sh.UsedRange.AutoFilter 1, vbYellow, xlFilterCellColor
End Sub
We can also use RGB color code in place of vbYellow.
After executing this code data will look like below image.
