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

learn advanced excel

Productivity and Automation | Excel VBA and Macros

VBA Code to Export Access Recordset to Excel

ByKailash Sharma

How to Export Access Data to Excel using VBA Code? Creating a VBA tool in MS Access is always better as compare to MS Excel. MS Access provides better user interface and ability to handle…

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

Excel VBA and Macros

VBA Code to Delete File

Byexcelsirji

In this article we are going to show you how you can delete file or files using a single line of VBA code. 1. Delete a specific file from the folder, 2. Delete specific type of files from the folder, 3. Delete all files from the folder

learn excel online free

Excel VBA and Macros

VBA Code to Browse a Folder

ByKailash Sharma

VBA Code to Browse a Folder Quite often a VBA developer requires code to browse a folder. This is mainly for saving the output file or reading the input file(s). Below is the VBA code…

VBA Code to Get User Domain Name

Excel VBA and Macros

VBA Code to Get User Domain Name

Byexcelsirji

Table of Content VBA Code to Get User Domain Name VBA Code to Get User Domain Name – Method 1 VBA Code to Get User Domain Name – Method 2 Steps to use this VBA…

excel basic training

Excel VBA and Macros | Productivity and Automation

VBA Code to Send Outlook Emails

ByKailash Sharma

VBA Code to send Outlook Emails Sending bulk emails is a very common activity, there are many office activities that need a person to send bulk emails to single or multiple recipients. You also may…

learn advanced excel

Productivity and Automation | Excel VBA and Macros

VBA Code to Export Access Recordset to Excel

ByKailash Sharma

How to Export Access Data to Excel using VBA Code? Creating a VBA tool in MS Access is always better as compare to MS Excel. MS Access provides better user interface and ability to handle…

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

Excel VBA and Macros

VBA Code to Delete File

Byexcelsirji

In this article we are going to show you how you can delete file or files using a single line of VBA code. 1. Delete a specific file from the folder, 2. Delete specific type of files from the folder, 3. Delete all files from the folder

learn excel online free

Excel VBA and Macros

VBA Code to Browse a Folder

ByKailash Sharma

VBA Code to Browse a Folder Quite often a VBA developer requires code to browse a folder. This is mainly for saving the output file or reading the input file(s). Below is the VBA code…

VBA Code to Get User Domain Name

Excel VBA and Macros

VBA Code to Get User Domain Name

Byexcelsirji

Table of Content VBA Code to Get User Domain Name VBA Code to Get User Domain Name – Method 1 VBA Code to Get User Domain Name – Method 2 Steps to use this VBA…

excel basic training

Excel VBA and Macros | Productivity and Automation

VBA Code to Send Outlook Emails

ByKailash Sharma

VBA Code to send Outlook Emails Sending bulk emails is a very common activity, there are many office activities that need a person to send bulk emails to single or multiple recipients. You also may…

learn advanced excel

Productivity and Automation | Excel VBA and Macros

VBA Code to Export Access Recordset to Excel

ByKailash Sharma

How to Export Access Data to Excel using VBA Code? Creating a VBA tool in MS Access is always better as compare to MS Excel. MS Access provides better user interface and ability to handle…

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

Excel VBA and Macros

VBA Code to Delete File

Byexcelsirji

In this article we are going to show you how you can delete file or files using a single line of VBA code. 1. Delete a specific file from the folder, 2. Delete specific type of files from the folder, 3. Delete all files from the folder

learn excel online free

Excel VBA and Macros

VBA Code to Browse a Folder

ByKailash Sharma

VBA Code to Browse a Folder Quite often a VBA developer requires code to browse a folder. This is mainly for saving the output file or reading the input file(s). Below is the VBA code…

VBA Code to Get User Domain Name

Excel VBA and Macros

VBA Code to Get User Domain Name

Byexcelsirji

Table of Content VBA Code to Get User Domain Name VBA Code to Get User Domain Name – Method 1 VBA Code to Get User Domain Name – Method 2 Steps to use this VBA…

excel basic training

Excel VBA and Macros | Productivity and Automation

VBA Code to Send Outlook Emails

ByKailash Sharma

VBA Code to send Outlook Emails Sending bulk emails is a very common activity, there are many office activities that need a person to send bulk emails to single or multiple recipients. You also may…

Leave a Reply Cancel reply

Excel VBA Master Tool Kit