VBA Code to Filter Data in Excel

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

Excel VBA Master Tool Kit

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

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

Method 1

'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.Range("A10000").End(xlUp).Row).AutoFilter 5, "Japan"

    'Apply second filter on 4th column (D) in the data where Department ID is 711
    Sheet1.Range("A1:G" & Sheet1.Range("A10000").End(xlUp).Row).AutoFilter 4, "711"

End Sub

Method 2

'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

VBA Code to Filter Data in Excel​, 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…’
VBA Code to Filter Data in Excel
  • Select ‘FilterData’ from the list and click on ‘Ok’ button
VBA Code to Filter Data in Excel​
  • Done, click on the shape to change the cell colors
VBA Code to filter data in excel

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.
Download Practice File

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

Happy Reading 🙂

Recommended Articles

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Excel VBA Master Tool Kit