Excel VBA and Macros Productivity and Automation

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

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

Done, click on the shape to change the cell colors

Download Practice File

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

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

Happy Reading

Recommended Articles

Protect Excel Sheet for Manual Input but Allow Programming Inputs

Excel VBA and Macros

Protect Excel Sheet for Manual Input but Allow Programming Inputs

Byexcelsirji

Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? Here is a simple code which can help you.

VBA Code to Add Items in ListBox Control in Userform

Excel VBA and Macros

VBA Code to Add Items in ListBox Control in Userform

Byexcelsirji

VBA Code To Add Items In Listbox Control Using ListBox in Userform is very common. You can use ListBox.AddItem function to add items in the listbox.; however, it is little difficult to add items in…

Excel VBA Tool to Get File Properties

Productivity and Automation

Excel VBA Tool to Get File Properties

Byexcelsirji

Excel VBA Tool To Get File Properties Here is one more interesting VBA tool from the ExcelSirJi team. File Properties Tool is an Excel VBA tool that gets the following properties of the file. File…

VBA Code to Sort Data

Excel VBA and Macros | Productivity and Automation

VBA Code to Sort Data

Byexcelsirji

VBA Code to Sort Data HERE IS A ONE LINE CODE FOR DEVELOPER’S REFERENCE WHICH CAN BE USED TO SORT DATA ‘Sort data in ascending order on Column F (Created At) Sheet1.Range(“A1:G” & Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row).Sort Key1:=Sheet1.Range(“F1”),…

Write Your First Macro in Excel

Excel VBA and Macros

Write Your First Macro in Excel

Byexcelsirji

Learn how to write your first VBA macro in Excel to automate repetitive formatting tasks across multiple worksheets. This beginner-friendly guide will walk you through the process step-by-step, from recording your actions to customizing the code for your specific needs.

Protect Excel Sheet for Manual Input but Allow Programming Inputs

Excel VBA and Macros

Protect Excel Sheet for Manual Input but Allow Programming Inputs

Byexcelsirji

Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? Here is a simple code which can help you.

VBA Code to Add Items in ListBox Control in Userform

Excel VBA and Macros

VBA Code to Add Items in ListBox Control in Userform

Byexcelsirji

VBA Code To Add Items In Listbox Control Using ListBox in Userform is very common. You can use ListBox.AddItem function to add items in the listbox.; however, it is little difficult to add items in…

Excel VBA Tool to Get File Properties

Productivity and Automation

Excel VBA Tool to Get File Properties

Byexcelsirji

Excel VBA Tool To Get File Properties Here is one more interesting VBA tool from the ExcelSirJi team. File Properties Tool is an Excel VBA tool that gets the following properties of the file. File…

VBA Code to Sort Data

Excel VBA and Macros | Productivity and Automation

VBA Code to Sort Data

Byexcelsirji

VBA Code to Sort Data HERE IS A ONE LINE CODE FOR DEVELOPER’S REFERENCE WHICH CAN BE USED TO SORT DATA ‘Sort data in ascending order on Column F (Created At) Sheet1.Range(“A1:G” & Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row).Sort Key1:=Sheet1.Range(“F1”),…

Write Your First Macro in Excel

Excel VBA and Macros

Write Your First Macro in Excel

Byexcelsirji

Learn how to write your first VBA macro in Excel to automate repetitive formatting tasks across multiple worksheets. This beginner-friendly guide will walk you through the process step-by-step, from recording your actions to customizing the code for your specific needs.

Protect Excel Sheet for Manual Input but Allow Programming Inputs

Excel VBA and Macros

Protect Excel Sheet for Manual Input but Allow Programming Inputs

Byexcelsirji

Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? Here is a simple code which can help you.

VBA Code to Add Items in ListBox Control in Userform

Excel VBA and Macros

VBA Code to Add Items in ListBox Control in Userform

Byexcelsirji

VBA Code To Add Items In Listbox Control Using ListBox in Userform is very common. You can use ListBox.AddItem function to add items in the listbox.; however, it is little difficult to add items in…

Excel VBA Tool to Get File Properties

Productivity and Automation

Excel VBA Tool to Get File Properties

Byexcelsirji

Excel VBA Tool To Get File Properties Here is one more interesting VBA tool from the ExcelSirJi team. File Properties Tool is an Excel VBA tool that gets the following properties of the file. File…

VBA Code to Sort Data

Excel VBA and Macros | Productivity and Automation

VBA Code to Sort Data

Byexcelsirji

VBA Code to Sort Data HERE IS A ONE LINE CODE FOR DEVELOPER’S REFERENCE WHICH CAN BE USED TO SORT DATA ‘Sort data in ascending order on Column F (Created At) Sheet1.Range(“A1:G” & Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row).Sort Key1:=Sheet1.Range(“F1”),…

Write Your First Macro in Excel

Excel VBA and Macros

Write Your First Macro in Excel

Byexcelsirji

Learn how to write your first VBA macro in Excel to automate repetitive formatting tasks across multiple worksheets. This beginner-friendly guide will walk you through the process step-by-step, from recording your actions to customizing the code for your specific needs.

Leave a Reply Cancel reply

Excel VBA Master Tool Kit