Excel Autofilter with VBA
Using the autofilter with VBA is an extremely powerful feature. The following traps all of the items in column A and adds that to the autofilter output. This is dynamic so as your Excel rows grow, the code adjusts accordingly.
The following YouTube video explains the process. The file to go with the videos is below.
Excel’s in built autofilter is one of the most powerful tools at your disposal in native Excel and VBA. Additionally the autofilter is one of the simplest tools to apply. Excel's Autofilters are extremely useful for isolating data. In VBA their worth is greatly enhanced as autofilters effectively avoid the use of loops when trapping a condition. Excel autofilters help the user isolate and move information between worksheets or workbooks with a minimum of fuss. Excel autofilters along with the use of SpecialCells outperform looping constructs in VBA because they isolate data in one movement while loops require several. In the majority of cases it's faster and more efficient to use one of Excel's built in features as opposed to re-inventing the wheel with VBA code created to test individual cell criteria.
One of the most valuable aspects of using autofilters within VBA is the advantage they have over iterating through a range of cells. If you have a few minutes to spare take 5,000 rows of data and iterate through the range isolating a single word and copying and pasting the data which meet the criteria to a different sheet. For every item which meets the criteria you have to perform 2 tasks. Effectively you are asking Excel to perform thousands of tasks by;
- Isolating the cells which meet our criteria.
- Copying and pasting those cells to a new sheet.
Which is done one cell at a time. Now do the same thing with an autofilter and see the difference in speed. By using an autofilter you are asking Excel to perform the same two tasks but the difference being you get the same result after the two tasks are complete. The autofilter will isolate the cells meeting your criteria and copy and paste the data to an adjacent sheet.
The following are some examples of autofilters I use on a regular basis to manipulate data. The construct for an autofilter is as follows.
YourRange.Autofilter Relevant Column, Criteria
This is a basic autofilter applied with Excel VBA on a static range.
Range("A1").AutoFilter 'Turn autofilter Off
The YourRange= Range("A1:E100")
Autofilter = Autofilter
Relevant Column =1 because we are using Column A
Criteria="Dept A"
So the code will filter all of the items in Column A which display the word Dept A.
The following filters data on the column which is most relevant. This is a preferred method, notice it is only filtering on one column.
Filter Using a Dynamic Range
The following traps all of the items in column A and adds that to the autofilter output. This is dynamic so as your Excel rows grow, the code adjusts accordingly.
Range("A1").AutoFilter 'Turn autoilter Off
Here is a second example which will delete all of the rows which are equal to zero.
Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12).EntireRow.Delete
Range("A1").AutoFilter 'Turn autofilter Off
Filter Trapping all of the Constants
Trap the constants in a list with Excel VBA. It assumes the autofilter is off when the procedure is first run.
Range("A1").AutoFilter 'Turn autofilter Off
Using the WildCard with a Filter
The following VBA procedure will filter all the items in Column A which do not contain the word Total and copy the contents to Sheet 2.
Range("A2:C" & Cells(Rows.Count, 3).End(xlUp).Row).Copy Sheet2.[a2]
[a1].AutoFilter 'Turn autofilter Off
Filter Using 2 Criteria in the Same Column with OR
The following will autofilter two conditions in one column. This helps you simply isolate more than 1 criterion with Excel VBA.
Range("A1").AutoFilter 'Turn autofilter Off
Filtering Data in a Range by Colour
The following will autofilter all of the items in Col A1:A10 which have an interior colour of red.
Filtering the Top 10 Items in a Column
The following will autofilter the top 10 items in Column E.
To change the amount of items which are included in the autofilter change the "10" to suit.
Filtering the Top 5 Percent in a Column
To autofilter a column by a percentage of the total items (5 for example) try the following.
To change the amount of items which is included in the Excel autofilter change the "5" to suit.
Filtering 2 Different Columns
To autofilter 2 different columns, these need to be filtered one at a time. I have made this next criteria a little more complex. The first criteria is will return all items in the list which do not equal England. The second line of code will autofilter only data which is Y. The example in the file will copy the data after the filter is applied to the summary sheet. It works quite nicely.
Range("E1:F100").AutoFilter 2, "Y"
I have attached an Excel sample file with all of the above. At this point my internet provider only allows me to upload Excel03 files. Some of the features mentioned above are XL 07 features or later (see multiple conditions). I have saved the Excel file as an XLS and it should all work on later versions of Excel.