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.

Filter Copy Data.xlsm


 
 

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.

Sub FilterBasic() 'Excel VBA autoilters items in Col A which Equal "Dept A"
Range("A1:E100").AutoFilter 1, "Dept A"
Range("A1").AutoFilter 'Turn autofilter Off
End Sub


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.


Sub FilterDynamicRng() 'Excel VBA dynamic range
Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "Dept A"
Range("A1").AutoFilter 'Turn autoilter Off
End Sub


Here is a second example which will delete all of the rows which are equal to zero.


Sub DelZero() 'Excel VBA dynamic range
Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, 0#
Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12).EntireRow.Delete
Range("A1").AutoFilter 'Turn autofilter Off
End Sub


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.


Sub FilterConstants() 'Excel VBA autofilter all of the constants in a given range, use “=” to autofilter blanks
Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "<>"
Range("A1").AutoFilter 'Turn autofilter Off
End Sub


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.


Sub FilterwithCard() 'Excel VBA to autofilter by word total
Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter 1, "<>" & "*Total"
Range("A2:C" & Cells(Rows.Count, 3).End(xlUp).Row).Copy Sheet2.[a2]
[a1].AutoFilter 'Turn autofilter Off
End Sub


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.


Sub Filter2Conditions() 'Excel VBA autofilter by 2 Criteria using xlOr code will not show autofilter arrows.
Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "Dept A", xlOr, "Dept B", 0
Range("A1").AutoFilter 'Turn autofilter Off
End Sub


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.


Sub FilterbyColour() 'Excel VBA autofilter by Items in Red.
Range("A1:A10").AutoFilter 1, vbRed, xlFilterCellColor
End Sub


Filtering the Top 10 Items in a Column

The following will autofilter the top 10 items in Column E.


Sub Top10() 'Excel VBA autofilter top 10
Range("E1:E100").AutoFilter 1, "10", xlTop10Items
End Sub

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.


Sub Top5() 'Excel VBA autofilter top 5%
Range("E1:E100").AutoFilter 1, "5", xlTop10Percent
End Sub

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.

Sub TwoColumns() 'Excel VBA to autofilter 2 columns
Range("E1:F100").AutoFilter 1, "<>England")
Range("E1:F100").AutoFilter 2, "Y"
End Sub

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.