Automatically Filter Data with Excel

Autofiltering data instantly is an article which I demonstrated with formula Filter with Formula.  In the following article I will show how to autofilter data with the help of an on change event.  The data will be autofiltered based on three criteria.  If any of these criteria are left blank this item will be not be included in the filter.  The items in blue in rows 10 to 12 will filter as the cell changes.

To achieve this task I will use a worksheet change event on the cells B10 to B12.  Each of these cells have a data validation list which will help to choose items.  By doing this you could restrict a larger list to the items in the data validation.  The following is the code;

Private Sub Worksheet_Change(ByVal Target As Range) 'Automatically Filter Data with Excel VBA
Dim i As Integer
If Not Intersect(Target, Range("B10:B12")) Is Nothing Then
For i=1 To 3
[A14:C14].AutoFilter i, IIf(Range("B" & i + 9)="", "<>", "=") & Range("B" & i + 9)
Next i
End If
End Sub

The code is basically saying that if any of the cells from B10 to B12 change then run the above procedure.  It loops through the 3 cells and evaluates if the cell has data in the cell.  If it does for example B10 has City of London then Column A will autofilter by the City of London as the first filter criteria.  If nothing is in B10 then all items in Column A will be displayed and the next criteria will be considered which is B11.  It will evaluate all criteria and autofilter accordingly.

I first solved this problem on Ozgrid, the link can be found here.  The following is the file to show workings.