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

VBA Code to Re-link MS Access Link Tables

Productivity and Automation

VBA Code to Re-link MS Access Link Tables

Byexcelsirji

In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location

VBA Code to Add Border to Excel Range

Excel VBA and Macros

VBA Code to Add Border to Excel Range

Byexcelsirji

Through formatting the cells, you can make your Excel data more beautiful and easier to understand. If you record a macro to add borders to Excel range, you will notice that it generates 30 plus lines of code for just a small work. Here we are sharing one line of code which does the same thing.

employee-database-management

Advanced Excel Techniques | Excel VBA and Macros | Productivity and Automation

Employee Database Tool

Byexcelsirji

Employee Database is an MS Access based tool to manage employee details. The tool supports upto 78 demographics for each employee such as Name, Location, Phone, Email, Address etc. The tool also comes with inbuilt attendance tracker to track daily attendance of employees. Over and above this, you can also design your own trackers and start using it.

ColorIndex in Excel VBA

Excel VBA and Macros

How to use ColorIndex in Excel VBA – Quick Guide

Byexcelsirji

Introduction :- ColorIndex in Excel VBA Today let’s try to understand how ColorIndex property in Excel VBA works. It is an easy and effective way to quickly complete the development. ColorIndex property is normally used…

vba code to read outlook emails

Excel VBA and Macros

VBA Code to Read Outlook Emails

ByKailash Sharma

VBA Code to Read Outlook Emails Reading emails from Outlook and capture them in Excel file is very common activity being performed in office environment. Doing this activity manually every time is quite boring and…

excel vba tool to rename files

Productivity and Automation | Advanced Excel Techniques | Excel VBA and Macros

Excel VBA Tool to Rename Files

Byexcelsirji

Free File Renamer Tool – Quickly Rename files batch using Excel VBA Here is another help code and tool for programmers to rename files. You can use this tool for renaming all files available in…

VBA Code to Re-link MS Access Link Tables

Productivity and Automation

VBA Code to Re-link MS Access Link Tables

Byexcelsirji

In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location

VBA Code to Add Border to Excel Range

Excel VBA and Macros

VBA Code to Add Border to Excel Range

Byexcelsirji

Through formatting the cells, you can make your Excel data more beautiful and easier to understand. If you record a macro to add borders to Excel range, you will notice that it generates 30 plus lines of code for just a small work. Here we are sharing one line of code which does the same thing.

employee-database-management

Advanced Excel Techniques | Excel VBA and Macros | Productivity and Automation

Employee Database Tool

Byexcelsirji

Employee Database is an MS Access based tool to manage employee details. The tool supports upto 78 demographics for each employee such as Name, Location, Phone, Email, Address etc. The tool also comes with inbuilt attendance tracker to track daily attendance of employees. Over and above this, you can also design your own trackers and start using it.

ColorIndex in Excel VBA

Excel VBA and Macros

How to use ColorIndex in Excel VBA – Quick Guide

Byexcelsirji

Introduction :- ColorIndex in Excel VBA Today let’s try to understand how ColorIndex property in Excel VBA works. It is an easy and effective way to quickly complete the development. ColorIndex property is normally used…

vba code to read outlook emails

Excel VBA and Macros

VBA Code to Read Outlook Emails

ByKailash Sharma

VBA Code to Read Outlook Emails Reading emails from Outlook and capture them in Excel file is very common activity being performed in office environment. Doing this activity manually every time is quite boring and…

excel vba tool to rename files

Productivity and Automation | Advanced Excel Techniques | Excel VBA and Macros

Excel VBA Tool to Rename Files

Byexcelsirji

Free File Renamer Tool – Quickly Rename files batch using Excel VBA Here is another help code and tool for programmers to rename files. You can use this tool for renaming all files available in…

VBA Code to Re-link MS Access Link Tables

Productivity and Automation

VBA Code to Re-link MS Access Link Tables

Byexcelsirji

In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location

VBA Code to Add Border to Excel Range

Excel VBA and Macros

VBA Code to Add Border to Excel Range

Byexcelsirji

Through formatting the cells, you can make your Excel data more beautiful and easier to understand. If you record a macro to add borders to Excel range, you will notice that it generates 30 plus lines of code for just a small work. Here we are sharing one line of code which does the same thing.

employee-database-management

Advanced Excel Techniques | Excel VBA and Macros | Productivity and Automation

Employee Database Tool

Byexcelsirji

Employee Database is an MS Access based tool to manage employee details. The tool supports upto 78 demographics for each employee such as Name, Location, Phone, Email, Address etc. The tool also comes with inbuilt attendance tracker to track daily attendance of employees. Over and above this, you can also design your own trackers and start using it.

ColorIndex in Excel VBA

Excel VBA and Macros

How to use ColorIndex in Excel VBA – Quick Guide

Byexcelsirji

Introduction :- ColorIndex in Excel VBA Today let’s try to understand how ColorIndex property in Excel VBA works. It is an easy and effective way to quickly complete the development. ColorIndex property is normally used…

vba code to read outlook emails

Excel VBA and Macros

VBA Code to Read Outlook Emails

ByKailash Sharma

VBA Code to Read Outlook Emails Reading emails from Outlook and capture them in Excel file is very common activity being performed in office environment. Doing this activity manually every time is quite boring and…

excel vba tool to rename files

Productivity and Automation | Advanced Excel Techniques | Excel VBA and Macros

Excel VBA Tool to Rename Files

Byexcelsirji

Free File Renamer Tool – Quickly Rename files batch using Excel VBA Here is another help code and tool for programmers to rename files. You can use this tool for renaming all files available in…

Leave a Reply Cancel reply

Excel VBA Master Tool Kit