Using AutoFilter techniques

Excel 2007 introduced the possibility of selecting multiple items from a filter, filtering by color, filtering by icon, filtering by top 10, and filtering to virtual date filters. Excel 2010 introduced the search box in the filter drop-down menu. All these filters have VBA equivalents, although some of them are implemented in VBA using legacy filtering methods.

Selecting multiple items

Legacy versions of Excel allowed you to select two values, joined by AND or OR. In this case, you would specify xlAnd or xlOr as the operator:

Click here to view code image

Range("A1").AutoFilter Field:=4, _
    Criteria1:="Ford", _
    Operator:=xlOr, _
    Criteria2:="General Motors"

As the AutoFilter command became more flexible, Microsoft continued to use the same three parameters, even if they didn’t quite make sense. For example, Excel still lets you filter a field by asking for the top five items or the bottom 8% of records. To use this type of filter, specify either "5" or "8" as the Criteria1 argument and then specify xlTop10Items, xlTop10Percent, xlBottom10Items, or xlBottom10Percent as the operator. For example, the following code produces the top 12 revenue records:

Click here to view code image

Sub Top10Filter()
    ' Top 12 Revenue Records
    Range("A1").AutoFilter Field:=6, _
      Criteria1:="12", _
      Operator:=xlTop10Items
End Sub

There are a lot of numbers (6, 12, 10) in the code for this AutoFilter. Field:=6 indicates that you are looking at the sixth column. xlTop10Items is the name of the filter, but the filter is not limited to 10 items. The criteria 12 indicates the number of items that you want the filter to return.

Excel offers several new filter options. It continues to force these filter options to fit in the old object model, where the filter command must fit in an operator and up to two criteria fields.

If you want to choose three or more items, change the operator to Operator: =xlFilterValues and specify the list of items as an array in the Criteria1 argument:

Click here to view code image

Range("A1").AutoFilter Field:=4, _
    Criteria1:=Array("General Motors", "Ford", "Fiat"), _
    Operator:=xlFilterValues

Range("A1").AutoFilter, Field:=4, Criteria1:="*at*"

Sub FilterByFontColor()
   Range("A1").AutoFilter Field:=6, _
    Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
End Sub

Sub FilterNoFontColor()
    Range("A1").AutoFilter Field:=6, _
     Operator:=xlFilterAutomaticFontColor
End Sub

Sub FilterByFillColor()
    Range("A1").AutoFilter Field:=6, _
     Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
End Sub

Filtering by icon

If you are expecting a data set to have an icon set applied, you can filter to show only records with one particular icon by using the xlFilterIcon operator.

For the criteria, you have to know which icon set has been applied, as well as which icon within the set you want to filter by. The icon sets are identified using the names shown in column A in Figure 11-2. The items range from 1 through 5. The following code filters the Revenue column to show the rows containing an upward-pointing arrow in the 5 Arrows Gray icon set:

Click here to view code image

Sub FilterByIcon()
    Range("A1").AutoFilter Field:=6, _
      Criteria1:=ActiveWorkbook.IconSets(xl5ArrowsGray).Item(5), _
      Operator:=xlFilterIcon
End Sub

To find records that have no conditional formatting icon, use the operator xlFilterNoIcon and do not specify criteria.

Sub DynamicAutoFilter()
    Range("A1").AutoFilter Field:=3, _
     Criteria1:=xlFilterNextYear, _
     Operator:=xlFilterDynamic
End Sub

Sub AutoFilterByCriteria2()
    Range("A1").AutoFilter Field:=3, _
     Criteria2:=Array(1, "12/01/2021", 0, "01/01/2022") , _
     Operator:=xlFilterValues
End Sub

Selecting visible cells only

After you apply a filter, most commands operate only on the visible rows in the selection. If you need to delete the records, format the records, or apply a conditional format to the records, you can simply refer to the .CurrentRegion of the first heading cell and perform the command.

However, if you have a data set in which the rows have been hidden using the Hide Rows command, any formatting applied to .CurrentRegion applies to the hidden rows, too. In these cases, you should use the Visible Cells Only option in the Go To Special dialog box, as shown in Figure 11-3.

To use Visible Cells Only in code, use the SpecialCells property:

Click here to view code image

Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)

Using the Excel interface to build an advanced filter

Because not many people use the Advanced Filter feature, this section walks you through examples using the user interface to build an advanced filter and then shows you the analogous code. You will be amazed at how complex the user interface seems and yet how easy it is to program a powerful advanced filter to extract records.

One reason Advanced Filter is hard to use is that you can use it in several different ways. Every Advanced Filter has to have a List Range. You must make three basic choices in the Advanced Filter dialog box. Because each choice has two options, there are eight (2 × 2 × 2) possible combinations of these choices. The three basic choices are shown in Figure 11-5 and described here:

• Action—You can select Filter The List, In-Place or you can select Copy To Another Location. If you choose to filter the records in place, the nonmatching rows are hidden. Choosing to copy to a new location copies the records that match the filter to a new range.

• Criteria—You can filter with or without criteria. Filtering with criteria is appropriate for getting a subset of rows. Filtering without criteria is still useful when you want a subset of columns or when you are using the Unique Records Only option.

• Unique—You can choose to request Unique Records Only or request all matching records. The Unique option makes using the Advanced Filter command one of the fastest ways to find a unique list of values in one field. By placing the Customer heading in the output range, you get a unique list of values for that one column.

Extracting a unique list of values with the user interface

To extract a unique list of values, follow these steps:

1. With the cursor anywhere in the data range, select Advanced from the Sort & Filter group on the Data tab. The first time you use the Advanced Filter command on a worksheet, Excel automatically populates the List Range text box with the entire range of your data set. On subsequent uses of the Advanced Filter command, this dialog box remembers the settings from the prior advanced filter.

2. Select the Unique Records Only check box at the bottom of the dialog box.

3. In the Action section, select Copy To Another Location.

4. Type J1 in the Copy To text box.

By default, Excel copies all the columns in the data set. You can filter just the Customer column either by limiting List Range to include only column D or by specifying one or more headings in the Copy To range. Each method has its own drawbacks.

Copying the customer heading before filtering

With a little thought before invoking the Advanced Filter command, you can allow Excel to keep the default list range $A$1:$H$1127. In cell J1, type the Customer heading, as shown in Figure 11-6. Leave the List Range field pointing to columns A through H. Because the Copy To range of J1 already contains a valid heading from the list range, Excel copies data only from the Customer column. This is the preferred method, particularly if you will be using multiple advanced filters. Because Excel remembers the settings from the preceding advanced filter, it is more convenient to always filter the entire columns of the list range and limit the columns by setting up headings in the Copy To range.

After you use either of these methods to perform the advanced filter, a concise list of the unique customers appears in column J (see Figure 11-6).

Extracting a unique list of values with VBA code

In VBA, you use the AdvancedFilter method to carry out the Advanced Filter command. Again, you have three choices to make:

• Action—Choose to either filter in place with the parameter Action:=xlFilterInPlace or copy with Action:=xlFilterCopy. If you want to copy, you also have to specify the parameter CopyToRange:=Range("J1").

• Criteria—To filter with criteria, include the parameter CriteriaRange:=Range("L1:L2"). To filter without criteria, omit this optional parameter.

• Unique—To return only unique records, specify the parameter Unique:=True.

The following code sets up a single-column output range two columns to the right of the last-used column in the data range:

Click here to view code image

Sub GetUniqueCustomers()
 Dim IRange As Range
 Dim ORange As Range
 
 ' Find the size of today's data set
 FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
 NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 2
 
 ' Set up the output range. Copy heading from D1 there
 Range("D1").Copy Destination:=Cells(1, NextCol)
 Set ORange = Cells(1, NextCol)
 
 ' Define the input range
 Set IRange = Range("A1").Resize(FinalRow, NextCol – 2)
 
 ' Use the Advanced Filter to get a unique list of customers
 IRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ORange, _
 Unique:=True
 
End Sub

By default, an advanced filter copies all columns. If you want just one particular column, use that column heading as the heading in the output range.

The first bit of preceding code finds the final row and column in the data set. Although it is not necessary to do so, you can define an object variable for the output range (Orange) and for the input range (Irange).

This code is generic enough that it will not have to be rewritten if new columns are added to the data set later. Setting up the object variables for the input and output range is done for readability rather than out of necessity. The previous code could be written just as easily like this shortened version:

Click here to view code image

Sub UniqueCustomerRedux()
    ' Copy a heading to create an output range
    Range("J1").Value = Range("D1").Value
   ' Use the Advanced Filter
    Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, _
     CopyToRange:=Range("J1"), Unique:=True
End Sub

When you run either of the previous blocks of code on the sample data set, you get a unique list of customers off to the right of the data. The key to getting a unique list of customers is copying the header from the Customer field to a blank cell and specifying this cell as the output range.

After you have the unique list of customers, you can sort the list and add a SUMIF formula to get total revenue by customer. The following code gets the unique list of customers, sorts it, and then builds a formula to total revenue by customer. Figure 11-7 shows the results:

Click here to view code image

Sub RevenueByCustomers()
    Dim Irange As Range
    Dim Orange As Range
 
    ' Find the size of today's data set
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 2
 
 ' Set up the output range. Copy the heading from D1 there
 Range("D1").Copy Destination:=Cells(1, NextCol)
 Set Orange = Cells(1, NextCol)
 
 ' Define the input range
 Set Irange = Range("A1").Resize(FinalRow, NextCol – 2)
 
 ' Use the Advanced Filter to get a unique list of customers
 Irange.AdvancedFilter Action:=xlFilterCopy, _
 CopyToRange:=Orange, Unique:=True
 
 ' Determine how many unique customers we have
 LastRow = Cells(Rows.Count, NextCol).End(xlUp).Row
 
 ' Sort the data
 Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, NextCol), _
 Order1:=xlAscending, Header:=xlYes
 
 ' Add a SUMIF formula to get totals
 Cells(1, NextCol + 1).Value = "Revenue"
 Cells(2, NextCol + 1).Resize(LastRow – 1).FormulaR1C1 = _
    "=SUMIF(R2C4:R" & FinalRow & _
    "C4,RC[-1],R2C6:R" & FinalRow & "C6)"
 
End Sub

Another use of a unique list of values is to quickly populate a list box or a combo box on a userform. For example, suppose that you have a macro that can run a report for any one specific customer. To allow your clients to choose which customers to report, create a simple userform. Add a list box to the userform and set the list box’s MultiSelect property to 1-fmMultiSelectMulti. In this case, the form is named frmReport. In addition to the list box, there are four command buttons: OK, Cancel, Mark All, and Clear All. The code to run the form follows. Note that the Userform_Initialize procedure includes an advanced filter to get the unique list of customers from the data set:

Click here to view code image

Private Sub CancelButton_Click()
    Unload Me
End Sub
 
Private Sub cbSubAll_Click()
    For I = 0 To lbCust.ListCount - 1
     Me.lbCust.Selected(i) = True
    Next i
End Sub
 
Private Sub cbSubClear_Click()
    For i = 0 To lbCust.ListCount - 1
     Me.lbCust.Selected(i) = False
    Next i
End Sub
 
Private Sub OKButton_Click()
    For i = 0 To lbCust.ListCount - 1
     If Me.lbCust.Selected(i) = True Then
      ' Call a routine (discussed later) to produce this report
      RunCustReport WhichCust:=Me.lbCust.List(i)
    End If
 Next i
 Unload Me
End Sub
 
Private Sub UserForm_Initialize()
 Dim iRange As Range
 Dim oRange As Range
 
 ' Find the size of today's data set
 FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
 NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 2
 
 ' Set up the output range. Copy the heading from D1 there
 Range("D1").Copy Destination:=Cells(1, NextCol)
 Set oRange = Cells(1, NextCol)
 
 ' Define the input range
 Set iRange = Range("A1").Resize(FinalRow, NextCol - 2)
 
 ' Use the Advanced Filter to get a unique list of customers
 iRange.AdvancedFilter Action:=xlFilterCopy, _
     CopyToRange:=oRange, Unique:=True
 
 ' Determine how many unique customers we have
 LastRow = Cells(Rows.Count, NextCol).End(xlUp).Row
 
 ' Sort the data
 Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, NextCol), _
 Order1:=xlAscending, Header:=xlYes
 
With Me.lbCust
 .RowSource = ""
 .List = Cells(2, NextCol).Resize(LastRow - 1, 1).Value
End With
 
 ' Erase the temporary list of customers
 Cells(1, NextCol).Resize(LastRow, 1).Clear
End Sub

Launch this form with a simple module, like this:

Sub ShowCustForm()
   frmReport.Show
End Sub

Your clients are presented with a list of all valid customers from the data set. Because the list box’s MultiSelect property is set to allow it, the clients can select any number of customers.

Getting unique combinations of two or more fields

To get all unique combinations of two or more fields, build the output range to include the additional fields. This code sample builds a list of unique combinations of two fields: Customer and Product:

Click here to view code image

Sub UniqueCustomerProduct()
    Dim iRange As Range
    Dim oRange As Range
 
    ' Find the size of today's data set
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 2
 
    ' Set up the output range. Copy headings from D1 & B1
    Range("D1").Copy Destination:=Cells(1, NextCol)
    Range("B1").Copy Destination:=Cells(1, NextCol + 1)
    Set oRange = Cells(1, NextCol).Resize(1, 2)
 
    ' Define the input range
     Set iRange = Range("A1").Resize(FinalRow, NextCol - 2)
 
    ' Use the Advanced Filter to get a unique list of customers & product
     iRange.AdvancedFilter Action:=xlFilterCopy, _
     CopyToRange:=oRange, Unique:=True
 
     ' Determine how many unique rows we have
     LastRow = Cells(Rows.Count, NextCol).End(xlUp).Row
 
    ' Sort the data
    Cells(1, NextCol).Resize(LastRow, 2).Sort Key1:=Cells(1, NextCol), _
    Order1:=xlAscending, Key2:=Cells(1, NextCol + 1), _
    Order2:=xlAscending, Header:=xlYes
 
End Sub

In the result shown in Figure 11-8, you can see that Enhanced Eggbeater buys only one product, and Distinctive Wax buys three products. This might be useful as a guide in running reports on either customer by product or product by customer.

Joining multiple criteria with a logical OR

You might want to filter records that match one criteria or another. For example, you can extract customers who purchased either product M556 or product W435. This is called a logical OR criteria.

When your criteria should be joined by a logical OR, place the criteria on subsequent rows of the criteria range. For example, the criteria range shown in J1:J3 in Figure 11-11 tells you which customers order product M556 or product W435.

Joining two criteria with a logical AND

Sometimes, you will want to filter records that match one criteria and another criteria. For example, you might want to extract records in which the product sold was W435 and the region was the West region. This is called a logical AND.

To join two criteria with AND, put both criteria on the same row of the criteria range. For example, the criteria range shown in J1:K2 in Figure 11-12 gets the customers who ordered product W435 in the West region.

Other slightly complex criteria ranges

The criteria range shown in Figure 11-13 is based on two different fields that are joined with an OR. The query finds all records that are from the West region or whose product is W435.

The most complex criteria: Replacing the list of values with a condition created as the result of a formula

It is possible to have a criteria range with multiple logical AND and logical OR criteria joined together. Although this might work in some situations, in other scenarios, it quickly gets out of hand. Fortunately, Excel allows for criteria in which the records are selected as the result of a formula to handle this situation.

Setting up a condition using computed criteria

Amazingly, there is an incredibly obscure version of Advanced Filter criteria that can replace the 44,000-row criteria range in the previous case study. In the alternative form of criteria range, the top row is left blank. There is no heading above the criteria. The criteria set up in row 2 is a formula that results in True or False. If the formula contains any relative references to row 2 of the data range, Excel compares that formula to every row of the data range, one by one.

For example, if you want all records in which Gross Profit Percentage is below 53%, the formula built in J2 references the profit in H2 and the revenue in F2. You need to leave J1 blank to tell Excel that you are using a computed criteria. Cell J2 contains the formula =(H2/F2)<0.53. The criteria range for the advanced filter would be specified as J1:J2.

As Excel performs the advanced filter, it logically copies the formula and applies it to all rows in the database. Anywhere that the formula evaluates to True, the record is included in the output range.

This is incredibly powerful and runs remarkably fast. You can combine multiple formulas in adjacent columns or rows to join the formula criteria with AND or OR, just as you do with regular criteria.

Using formula-based conditions with VBA

Referring back to the userform shown in Figure 11-14, you can use formula-based conditions to filter the report using the userform. The following is the code for this userform. Note the logic in OKButton_Click that builds the formula. Figure 11-17 shows the Excel sheet just before the advanced filter is run.

The following code initializes the userform. Three advanced filters find the unique list of customers, products, and regions:

Click here to view code image

Private Sub UserForm_Initialize()
   Dim IRange As Range
   Dim ORange As Range
 
    '  Find the size of today's data set
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 2
 
    '  Define the input range
    Set IRange = Range("A1").Resize(FinalRow, NextCol – 2)
 
    ' Set up the output range for Customer. Copy the heading from D1 there
    Range("D1").Copy Destination:=Cells(1, NextCol)
    Set ORange = Cells(1, NextCol)
 
    '  Use the Advanced Filter to get a unique list of customers
    IRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= "", _
    CopyToRange:=ORange, Unique:=True
 
    '  Determine how many unique customers we have
    LastRow = Cells(Rows.Count, NextCol).End(xlUp).Row
 
    '  Sort the data
    Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, NextCol), _
    Order1:=xlAscending, Header:=xlYes
 
    With Me.lbCust
    .RowSource = ""
    .List = Application.Transpose( _
    Cells(2,NextCol).Resize(LastRow-1,1))
    End With
 
    '  Erase the temporary list of customers
     Cells(1, NextCol).Resize(LastRow, 1).Clear
 
    '  Set up an output range for the product. Copy the heading from D1 there
    Range(" B1 ").Copy Destination:=Cells(1, NextCol)
    Set ORange = Cells(1, NextCol)
 
    '  Use the Advanced Filter to get a unique list of customers
    IRange.AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=ORange, Unique:=True
 
    '  Determine how many unique customers we have
     LastRow = Cells(Rows.Count, NextCol).End(xlUp).Row
 
    '  Sort the data
    Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, NextCol), _
    Order1:=xlAscending, Header:=xlYes
 
    With Me.lbProduct
    .RowSource = ""
    '  The list has to go across, so transpose the vertical data.
    .List = Application.Transpose( _
    Cells(2,NextCol).Resize(LastRow-1,1))
    End With
 
    '  Erase the temporary list of customers
    Cells(1, NextCol).Resize(LastRow, 1).Clear
 
    '  Set up the output range for Region. Copy the heading from A1 there
    Range(" A1 ").Copy Destination:=Cells(1, NextCol)
    Set Orange = Cells(1, NextCol)
 
    '  Use the Advanced Filter to get a unique list of customers
    IRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ORange, _
    Unique:=True
 
    '  Determine how many unique customers we have
    LastRow = Cells(Rows.Count, NextCol).End(xlUp).Row
 
    '  Sort the data
    Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, NextCol), _
       Order1:=xlAscending, Header:=xlYes
 
    With Me.lbRegion
      .RowSource = ""
      .List = Application.Transpose( _
       Cells(2,NextCol).Resize(LastRow-1,1))
    End With
 
    '  Erase the temporary list of customers
    Cells(1, NextCol).Resize(LastRow, 1).Clear
End Sub

These tiny procedures run when someone clicks Mark All or Clear All in the userform in Figure 11-14:

Click here to view code image

Private Sub CancelButton_Click()
    Unload Me
End Sub
 
Private Sub cbSubAll_Click()
    For I = 0 To lbCust.ListCount - 1
     Me.lbCust.Selected(i) = True
    Next i
End Sub
 
Private Sub cbSubClear_Click()
    For i = 0 To lbCust.ListCount - 1
     Me.lbCust.Selected(i) = False
    Next i
End Sub
 
Private Sub CommandButton1_Click()
    ' Clear all products
    For i = 0 To lbProduct.ListCount - 1
     Me.lbProduct.Selected(i) = False
   Next i
End Sub
 
Private Sub CommandButton2_Click()
    ' Mark all products
     For i = 0 To lbProduct.ListCount - 1
      Me.lbProduct.Selected(i) = True
    Next i
End Sub
 
Private Sub CommandButton3_Click()
    ' Clear all regions
    For i = 0 To lbRegion.ListCount - 1
     Me.lbRegion.Selected(i) = False
    Next i
End Sub
 
Private Sub CommandButton4_Click()
    ' Mark all regions
    For i = 0 To lbRegion.ListCount - 1
     Me.lbRegion.Selected(i) = True
    Next i
End Sub

The following code is attached to the OK button. This code builds three ranges in O, P, and Q that list the selected customers, products, and regions. The actual criteria range is composed of three blank cells in J1:L1 and then three formulas in J2:L2:

Click here to view code image

Private Sub OKButton_Click()
    Dim CRange As Range, IRange As Range, ORange As Range
   '  Build a complex criteria that ANDs all choices together
    NextCCol = 10
    NextTCol = 15
 
    For j = 1 To 3
    Select Case j
      Case 1
       MyControl = " lbCust"
       MyColumn = 4
     Case 2
        MyControl = "lbProduct"
        MyColumn = 2
 Case 3
        MyControl = "lbRegion"
        MyColumn = 1
 End Select
 NextRow = 2
 '  Check to see what was selected.
 For i = 0 To Me.Controls(MyControl).ListCount - 1
 If Me.Controls(MyControl).Selected(i) = True Then
     Cells(NextRow, NextTCol).Value = _
       Me.Controls(MyControl).List(i)
     NextRow = NextRow + 1
 End If
 Next i
 '  If anything was selected, build a new criteria formula
 If NextRow > 2 Then
    ' the reference to Row 2 must be relative in order to work
     MyFormula = "=NOT(ISNA(MATCH(RC" & MyColumn & ",R2C" & _
       NextTCol & ":R" & NextRow - 1 & "C" & NextTCol & ",0))) "
     Cells(2, NextCCol).FormulaR1C1 = MyFormula
     NextTCol = NextTCol + 1
     NextCCol = NextCCol + 1
   End If
 Next j
 Unload Me
 
 '  Figure 11-17 shows the worksheet at this point
 '  If we built any criteria, define the criteria range
 If NextCCol > 10 Then
     Set cRange = Range(Cells(1, 10), Cells(2, NextCCol - 1))
     Set IRange = Range("A1").CurrentRegion
     Set ORange = Cells(1, 20)
     IRange.AdvancedFilter xlFilterCopy, CRange, ORange
 
 '  Clear out the criteria
 Cells(1, 10).Resize(1, 10).EntireColumn.Clear
 End If
 
 '  At this point, the matching records are in T1
 
End Sub

Figure 11-17 shows the worksheet just before the AdvancedFilter method is called. The user has selected customers, products, and regions. The macro has built temporary tables in columns O, P, and Q to show which values the user selected. The criteria range is J1:L2. The criteria formula in J2 looks to see whether the value in $D2 is in the list of selected customers in O. The formulas in K2 and L2 compare $B2 to column P and $A2 to column Q.

Note

Excel VBA Help says that if you do not specify a criteria range, no criteria are used. This is not true in Excel 2013, 2016, and 2019. If no criteria range is specified in these versions of Excel, the advanced filter inherits the criteria range from the prior advanced filter. You should include CriteriaRange:="" to clear the prior value.

=$A2>Average($A$2:$A$1048576)

Catching no records when using a filter in place

Just as when using Copy, you have to watch out for the possibility of having no records match the criteria. However, in this case, it is more difficult to realize that nothing is returned. You generally find out when the .SpecialCells method returns a runtime error 1004, which indicates that no cells were found.

To catch this condition, you have to set up an error trap to anticipate the 1004 error with the SpecialCells method:

Click here to view code image

On Error GoTo NoRecs
   For Each cell In Range("A2:A" & FinalRow).SpecialCells(xlCellTypeVisible)
     Ctr = Ctr + 1
   Next cell
   On Error GoTo 0
   MsgBox Ctr & " cells match the criteria"
   Exit Sub
NoRecs:
    MsgBox "No records match the criteria"
End Sub

This error trap works because it specifically excludes the header row from the SpecialCells range. The header row is always visible after an advanced filter. Including it in the range would prevent the 1004 error from being raised.

Showing all records after running a filter in place

After doing a filter in place, you can get all records to show again by using the ShowAllData method:

ActiveSheet.ShowAllData

Copying all columns

To copy all columns, specify a single blank cell as the output range. You get all columns for those records that match the criteria, as shown in Figure 11-18:

Click here to view code image

Sub AllColumnsOneCustomer()
    Dim iRange As Range
    Dim oRange As Range
    Dim cRange As Range
 
    ' Find the size of today's data set
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 2
 
    ' Set up the criteria range with one customer
    Cells(1, NextCol).Value = Range("D1").Value
    ' In reality, this value should be passed from the userform
    Cells(2, NextCol).Value = Range("D2").Value
    Set cRange = Cells(1, NextCol).Resize(2, 1)
 
    ' Set up the output range. It is a single blank cell
    Set oRange = Cells(1, NextCol + 2)
 
    ' Define the input range
     Set iRange = Range("A1").Resize(FinalRow, NextCol - 2)
 
    ' Use the Advanced Filter to get a unique list of customers & product
    iRange.AdvancedFilter Action:=xlFilterCopy, _
     CriteriaRange:=cRange, CopyToRange:=Orange
 
End Sub

Copying a subset of columns and reordering

If you are doing an advanced filter to send records to a report, it is likely that you might need only a subset of columns, and you might need them in a different sequence.

This example finishes the frmReport example that was presented earlier in this chapter. As you recall, frmReport allows the client to select a customer. The OK button then calls the RunCustReport routine, passing a parameter to identify for which customer to prepare a report.

Imagine that this is a report being sent to the customer. The customer really does not care about the surrounding region, and you do not want to reveal your cost of goods sold or profit. Assuming that you will put the customer’s name in the title of the report, the fields that you need in order to produce the report are Date, Quantity, Product, and Revenue.

The following code copies those headings to the output range:

Click here to view code image

Sub RunCustReport(WhichCust As Variant)
     Dim Irange As Range
     Dim Orange As Range
     Dim Crange As Range
     Dim WBN As Workbook
     Dim WSN As Worksheet
     Dim WSO As Worksheet
 
    Set WSO = ActiveSheet
     ' Find the size of today's data set
     FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
     NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 2
 
     ' Set up the criteria range with one customer
     Cells(1, NextCol).Value = Range("D1").Value
     Cells(2, NextCol).Value = WhichCust
     Set Crange = Cells(1, NextCol).Resize(2, 1)
 
     ' Set up the output range. We want Date, Quantity, Product, Revenue
     ' These columns are in C, E, B, and F
     Cells(1, NextCol + 2).Resize(1, 4).Value = _
     Array(Cells(1, 3), Cells(1, 5), Cells(1, 2), Cells(1, 6))
     Set Orange = Cells(1, NextCol + 2).Resize(1, 4)
 
     ' Define the input range
     Set Irange = Range("A1").Resize(FinalRow, NextCol – 2)
 
     ' Use the Advanced Filter to get a unique list of customers & products
     Irange.AdvancedFilter Action:=xlFilterCopy, _
     CriteriaRange:=Crange, CopyToRange:=Orange
 
     ' Create a new workbook with one blank sheet to hold the output
     ' xlWBATWorksheet is the template name for a single worksheet
     Set WBN = Workbooks.Add(xlWBATWorksheet)
     Set WSN = WBN.Worksheets(1)
 
     ' Set up a title on WSN
     WSN.Cells(1, 1).Value = "Report of Sales to " & WhichCust
 
     ' Copy data from WSO to WSN
     WSO.Cells(1, NextCol + 2).CurrentRegion.Copy _
     Destination:=WSN.Cells(3, 1)
     TotalRow = WSN.Cells(Rows.Count, 1).End(xlUp).Row + 1
     WSN.Cells(TotalRow, 1).Value = "Total"
     WSN.Cells(TotalRow, 2).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
     WSN.Cells(TotalRow, 4).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
 
     ' Format the new report with bold
     WSN.Cells(3, 1).Resize(1, 4).Font.Bold = True
     WSN.Cells(TotalRow, 1).Resize(1, 4).Font.Bold = True
     WSN.Cells(1, 1).Font.Size = 18
 
     WBN.SaveAs ThisWorkbook.Path & Application.PathSeparator & _
     WhichCust & ".xlsx"
     WBN.Close SaveChanges:=False
 
     WSO.Select
 
     ' clear the output range, etc.
     Range("J:Z").Clear
 
End Sub

The advanced filter produces data, as shown in Figure 11-19. The program then goes on to copy the matching records to a new workbook. A title and a total row are added, and the report is saved with the customer’s name. Figure 11-20 shows the final report.

Excel in practice: Turning off a few drop-down menus in the AutoFilter

A really cool trick is possible only in Excel VBA. When you AutoFilter a list in the Excel user interface, every column in the data set gets a field drop-down arrow in the heading row. Sometimes you have a field that does not make a lot of sense to AutoFilter. For example, in your current data set, you might want to provide AutoFilter drop-down menus for Region, Product, and Customer but not the numeric or date fields. After setting up the AutoFilter, you need one line of code to turn off each drop-down menu that you do not want to appear. The following code turns off the drop-down menus for columns C, E, F, G, and H:

Click here to view code image

Sub AutoFilterCustom()
 Range("A1").AutoFilter Field:=3, VisibleDropDown:=False
 Range("A1").AutoFilter Field:=5, VisibleDropDown:=False
 Range("A1").AutoFilter Field:=6, VisibleDropDown:=False
 Range("A1").AutoFilter Field:=7, VisibleDropDown:=False
 Range("A1").AutoFilter Field:=8, VisibleDropDown:=False
End Sub

Using this tool is a fairly rare treat. Most of the time, Excel VBA lets you do things that are possible in the user interface—and lets you do them rapidly. The VisibleDropDown parameter actually enables you to do something in VBA that is generally not available in the Excel user interface. Your knowledgeable clients will be scratching their heads, trying to figure out how you set up the cool automatic filter with only a few filterable columns (see Figure 11-21).

To clear the filter from the customer column, use this code:

Click here to view code image

Sub SimpleFilter()
 Worksheets("SalesReport").Select
 Range("A1").AutoFilter
 Range("A1").AutoFilter Field:=4
End Sub