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 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

FilterData
'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
34.3
  • Done, click on the shape to change the cell colors
34.4

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

Happy Reading 🙂

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.

Leave a Reply

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