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 🙂

Similar Posts

Leave a Reply

Your email address will not be published.