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

learn excel free

Excel VBA and Macros

VBA Code to Draft Outlook Emails

ByKailash Sharma

How to send bulk emails from outlook using excel VBA? Have you ever felt the need of an Excel based VBA tool or code which can help you to draft Outlook emails in bulk by…

VBA Code to Check if File Exist in Folder

Productivity and Automation

VBA Code to Check if File Exist in Folder

Byexcelsirji

To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.

VBA Code to Protect Excel Sheet for Manual Input but Allow Programming Inputs

Excel VBA and Macros | Productivity and Automation

VBA Code to 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 here.

Outlook-Email-Management-Tool

Advanced Excel Techniques | Excel VBA and Macros

Outlook Email Management Tool

Byexcelsirji

Outlook Email Management Tool is an Excel based tool which works with Outlook 2010 or above version and helps you to read, copy or move multiple emails from an Outlook folder and sub-folders on click of a button. The tool also supports extracting attachments from emails as well.

VBA to Read Excel Data Using Connection String

Excel VBA and Macros | Productivity and Automation

VBA code to Read Excel Data using Connection String

Byexcelsirji

VBA to Read Excel Data Using Connection String Sometimes as a programmer you need to read heavy (more then 5 MB) Excel files. There are two ways you can read data from Excel files: Open…

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…

learn excel free

Excel VBA and Macros

VBA Code to Draft Outlook Emails

ByKailash Sharma

How to send bulk emails from outlook using excel VBA? Have you ever felt the need of an Excel based VBA tool or code which can help you to draft Outlook emails in bulk by…

VBA Code to Check if File Exist in Folder

Productivity and Automation

VBA Code to Check if File Exist in Folder

Byexcelsirji

To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.

VBA Code to Protect Excel Sheet for Manual Input but Allow Programming Inputs

Excel VBA and Macros | Productivity and Automation

VBA Code to 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 here.

Outlook-Email-Management-Tool

Advanced Excel Techniques | Excel VBA and Macros

Outlook Email Management Tool

Byexcelsirji

Outlook Email Management Tool is an Excel based tool which works with Outlook 2010 or above version and helps you to read, copy or move multiple emails from an Outlook folder and sub-folders on click of a button. The tool also supports extracting attachments from emails as well.

VBA to Read Excel Data Using Connection String

Excel VBA and Macros | Productivity and Automation

VBA code to Read Excel Data using Connection String

Byexcelsirji

VBA to Read Excel Data Using Connection String Sometimes as a programmer you need to read heavy (more then 5 MB) Excel files. There are two ways you can read data from Excel files: Open…

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…

learn excel free

Excel VBA and Macros

VBA Code to Draft Outlook Emails

ByKailash Sharma

How to send bulk emails from outlook using excel VBA? Have you ever felt the need of an Excel based VBA tool or code which can help you to draft Outlook emails in bulk by…

VBA Code to Check if File Exist in Folder

Productivity and Automation

VBA Code to Check if File Exist in Folder

Byexcelsirji

To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.

VBA Code to Protect Excel Sheet for Manual Input but Allow Programming Inputs

Excel VBA and Macros | Productivity and Automation

VBA Code to 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 here.

Outlook-Email-Management-Tool

Advanced Excel Techniques | Excel VBA and Macros

Outlook Email Management Tool

Byexcelsirji

Outlook Email Management Tool is an Excel based tool which works with Outlook 2010 or above version and helps you to read, copy or move multiple emails from an Outlook folder and sub-folders on click of a button. The tool also supports extracting attachments from emails as well.

VBA to Read Excel Data Using Connection String

Excel VBA and Macros | Productivity and Automation

VBA code to Read Excel Data using Connection String

Byexcelsirji

VBA to Read Excel Data Using Connection String Sometimes as a programmer you need to read heavy (more then 5 MB) Excel files. There are two ways you can read data from Excel files: Open…

Leave a Reply Cancel reply

Excel VBA Master Tool Kit