VBA Code to Filter Data in Excel

Here is an easy reference code which filters data in the sheet. In the code, we have filtered the data in three steps.

Step 1: Remove existing filter from the sheet (if any). This code ensures if there is already a filter applied then the same is removed first

Step 2: Apply filter on 5^th column (E) of the data where Country is Japan

Step 3: Apply filter on 4^th column (D) of the data where Department ID is 711

'This function can be used to filter data
 Public Sub FilterData()
     '
     'Reset/Remove filter from sheet
     Sheet1.AutoFilterMode = False
     '
     'Apply first filter on 5th column (E) in the data where Country is Japan
     Sheet1.Range("A1:G" & Sheet1.UsedRange.Row).AutoFilter 5, "Japan"
     'Apply second filter on 4th column (D) in the data where Department ID is 711
     Sheet1.Range("A1:G" & Sheet1.UsedRange.Row).AutoFilter 4, "711"
     '
 End Sub 

To use this code in your Excel file, follow below steps:

• Open an Excel file
• Press Alt+F11
• Insert a Module (Insert>Module) from menu bar
• Paste the code in the module
• Now add a shape in Excel sheet
• Give a name to the shape like ‘Filter Data’
• Right click on the shape and select ‘Assign Macro…’

• Select ‘FilterData’ from the list and click on ‘Ok’ button

• Done, click on the shape to change the cell colors

Hope You like this article. Please share this with your friends and colleagues.

Happy Reading 🙂