CHAPTER 11
Data mining with Advanced Filter

Read this chapter.

Although very few people use Advanced Filter in Excel, it is a workhorse in Excel VBA. I estimate that I end up using one of these filtering techniques as the core of a macro in 80% of the macros I develop for clients. Given that Advanced Filter is used in fewer than 1% of Excel sessions, this is a dramatic statistic.

So even if you hardly ever use Advanced Filter in regular Excel, you should study this chapter to learn some powerful VBA techniques.

Replacing a loop with AutoFilter

In Chapter 4, “Looping and flow control,” you read about several ways to loop through a data set to format records that match certain criteria. By using Filter (Microsoft’s name for what was originally AutoFilter), you can achieve the same result much faster. While other examples in this chapter use the Advanced Filter, this example can be solved with the simpler Filter. Although Microsoft changed the name of AutoFilter to Filter in Excel 2007, the VBA code still refers to AutoFilter.

When AutoFilter was added to Excel, the team at Microsoft added extra care and attention to it. Items hidden because of AutoFilter are not simply treated like other hidden rows. AutoFilter gets special treatment. You’ve likely run into the frustrating situation in the past where you have applied formatting to visible rows and the formatting has gotten applied to the hidden rows. This is certainly a problem when you’ve hidden rows by clicking the #2 Group and Outline button after using the Subtotal command. This is always a problem when you manually hide rows. But it is never a problem when the rows are hidden because of AutoFilter.

After you’ve applied AutoFilter to hide rows, any action performed on the CurrentRegion is applied only to the visible rows. You can apply bold. You can change the font to red. You can even use CurrentRegion.EntireRow.Delete to delete the visible rows and not affect the rows hidden by the filter.

Let’s say that you have a data set like the one shown in Figure 11-1, and you want to perform some action on all the records that match a certain criteria, such as all Ford records.

In Chapter 5, “R1C1-style formulas,” you learned to write code like the following, which you could use to color all the Ford records green:

Click here to view code image

Sub OldLoop()

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To FinalRow

If Cells(i, 4) = "Ford" Then

Cells(i, 1).Resize(1, 8).Interior.Color = RGB(0,255,0)

End If

Next i

End Sub

If you needed to delete records, you had to be careful to run the loop from the bottom of the data set to the top, using code like this:

Click here to view code image

Sub OldLoopToDelete()

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = FinalRow To 2 Step -1

If Cells(i, 4) = "Ford" Then

Rows(i).Delete

End If

Next i

End Sub

The AutoFilter method, however, enables you to isolate all the Ford records in a single line of code:

Click here to view code image

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

After isolating the matching records, you do not need to use the VisibleCellsOnly setting to format the matching records. Instead, you can use the following line of code to make all the matching records green:

Click here to view code image

Range("A1").CurrentRegion.Interior.Color = RGB(0,255,0)

There are two problems with the current two-line macro. First, the program leaves the AutoFilter drop-down menus in the data set. Second, the heading row is also formatted in green.

This single line of code turns off the AutoFilter drop-down menus and clears the filter:

Range("A1").AutoFilter

If you want to leave the AutoFilter drop-down menus on but clear the column D drop-down menu from showing Ford, you can use this line of code:

ActiveSheet.ShowAllData

Addressing the second problem is a bit more difficult. After you apply the filter and select Range("A1").CurrentRegion, the selection automatically includes the headers in the selection. Any formatting is also applied to the header row.

If you do not care about the first blank row below the data, you can simply add OFFSET(1) to move the current region down to start in A2. This would be fine if your goal were to delete all the Ford records:

Click here to view code image

Sub DeleteFord()

' skips header, but also deletes blank row below

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

Range("A1").CurrentRegion.Offset(1).EntireRow.Delete

Range("A1").AutoFilter

End Sub

The preceding code works when you do not mind if the first blank row below the data is deleted. However, when you apply a green format to those rows, the code applies the green format to the blank row below the data set, and that would not look right.

If you will be doing some formatting, you can determine the height of the data set and use .Resize to reduce the height of the current region while you use OFFSET:

Click here to view code image

Sub ColorFord()

DataHt = Range("A1").CurrentRegion.Rows.Count

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

Click here to view code image

With Range("A1").CurrentRegion.Offset(1).Resize(DataHt - 1)

' No need to use VisibleCellsOnly for formatting

.Interior.Color = RGB(0,255,0)

.Font.Bold = True

End With

' Clear the AutoFilter & remove drop-downs

Range("A1").AutoFilter

End Sub

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

Selecting using the Search box

Excel 2010 introduced the Search box in the AutoFilter drop-down menu. After typing something in the Search box, you can use the Select All Search Results item.

The macro recorder does a poor job of recording the Search box. The macro recorder hard-codes a list of customers who matched the search at the time you ran the macro.

Think about the Search box. It is really a shortcut way of selecting Text Filters, Contains. Furthermore, the Contains filter is actually a shortcut way of specifying the search string surrounded by asterisks. Therefore, to filter to all the records that contain “at,” use this:

Click here to view code image

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

Filtering by color

To find records that have a particular font color, use the operator xlFilterFontColor and specify a particular RGB value as the criteria. This code finds all cells with a red font in column F:

Click here to view code image

Sub FilterByFontColor()

Range("A1").AutoFilter Field:=6, _

Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor

End Sub

To find records that have no particular font color, use the operator xlFilterAutomaticFillColor and do not specify criteria:

Click here to view code image

Sub FilterNoFontColor()

Range("A1").AutoFilter Field:=6, _

Operator:=xlFilterAutomaticFontColor

End Sub

To find records that have a particular fill color, use the operator xlFilterCellColor and specify a particular RGB value as the criteria. This code finds all red cells in column F:

Click here to view code image

Sub FilterByFillColor()

Range("A1").AutoFilter Field:=6, _

Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor

End Sub

To find records that have no fill color, use the operator xlFilterNoFill and do not specify criteria.

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.

Selecting a dynamic date range using AutoFilters

Perhaps the most powerful feature in the world of Excel filters is the dynamic filters. These filters enable you to choose records that are above average or with a date field to select virtual periods, such as next week or last year.

To use a dynamic filter, specify xlFilterDynamic as the operator and then use 1 of 34 values as Criteria1. The following code finds all dates that are in the next year:

Click here to view code image

Sub DynamicAutoFilter()

Range("A1").AutoFilter Field:=3, _

Criteria1:=xlFilterNextYear, _

Click here to view code image

Operator:=xlFilterDynamic

End Sub

The following are all the dynamic filter criteria options, which you specify as Criteria1 in the AutoFilter method:

• Criteria for values—Use xlFilterAboveAverage or xlFilterBelowAverage to find all the rows that are above or below average.

• Criteria for future periods—Use xlFilterTomorrow, xlFilterNextWeek, xlFilterNextMonth, xlFilterNextQuarter, or xlFilterNextYear to find rows that fall in a certain future period. Note that “next week” starts on Sunday and ends on Saturday.

• Criteria for current periods—Use xlFilterToday, xlFilterThisWeek, xlFilterThisMonth, xlFilterThisQuarter, or xlFilterThisYear to find rows that fall within the current period. Excel uses the system clock to find the current day.

• Criteria for past periods—Use xlFilterYesterday, xlFilterLastWeek, xlFilterLastMonth, xlFilterLastQuarter, xlFilterLastYear, or xlFilterYearToDate to find rows that fall within a previous period.

• Criteria for specific quarters—Use xlFilterDatesInPeriodQuarter1, xlFilterDatesInPeriodQuarter2, xlFilterDatesInPeriodQuarter3, or xlFilterDatesInPeriodQuarter4 to filter to rows that fall within a specific quarter. Note that these filters do not differentiate based on a year. If you ask for quarter 1, you might get records from this January, last February, and next March.

• Criteria for specific months—Use xlFilterDatesInPeriodJanuary through xlFilterDatesInPeriodDecember to filter to records that fall during a certain month. As with quarters, the filter does not filter to any particular year.

Unfortunately, you cannot combine criteria. You might think that you can specify xlFilterDatesInPeriodJanuary as Criteria1 and xlFilterDatesNextYear as Criteria2. Even though this is a brilliant thought, Microsoft does not support this syntax (yet).

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)

Advanced Filter—easier in VBA than in Excel

Using the arcane Advanced Filter command is so difficult in the Excel user interface that it is pretty rare to find someone who enjoys using it regularly.

However, in VBA, advanced filters are a joy to use. With a single line of code, you can rapidly extract a subset of records from a database or quickly get a unique list of values in any column. This is critical when you want to run reports for a specific region or customer. Two advanced filters are used most often in the same procedure—one to get a unique list of customers and a second to filter to each customer, as shown in Figure 11-4. The rest of this chapter builds toward such a routine.

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.

Using Advanced Filter to extract a unique list of values

One of the simplest uses of Advanced Filter is to extract a unique list of a single field from a data set. In this example, you want to get a unique list of customers from a sales report. You know that Customer is in column D of the data set. You have an unknown number of records starting in cell A2, and row 1 is the header row. There is nothing located to the right of the data set.

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.

Changing the list range to a single column

Edit List Range to point to the Customer column. In this case, you need to change the default $A$1:$H$1127 to $D$1:$D$1127. The Advanced Filter dialog box should appear.

The drawback of this method is that Excel remembers the list range on subsequent uses of the Advanced Filter command. If you later want to get a unique list of regions, you will be constantly specifying the list range.

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 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

Click here to view code image

' 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

Click here to view code image

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

Click here to view code image

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.

Using Advanced Filter with criteria ranges

As the name implies, Advanced Filter is usually used to filter records—in other words, to get a subset of data. You specify the subset by setting up a criteria range.

Set up a criteria range in a blank area of a worksheet. A criteria range always includes two or more rows. The first row of the criteria range contains one or more field header values to match the one(s) in the data range you want to filter. The second row contains a value showing which records to extract. In Figure 11-9, J1:J2 is the criteria range, and L1 is the output range.

In the Excel user interface, to extract a unique list of products that were purchased by a particular customer, select Advanced Filter and set up the Advanced Filter dialog box as shown in Figure 11-9. Figure 11-10 shows the results.

You can use the following VBA code to perform an equivalent advanced filter:

Click here to view code image

Sub UniqueProductsOneCustomer()

Dim IRange As Range

Dim ORange As Range

Dim CRange As Range

' Find the size of today's data set

Click here to view code image

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 2

' Set up the output 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. Copy the heading from B1 there

Range("B1").Copy Destination:=Cells(1, NextCol + 2)

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, Unique:=True

' The above could also be written as:

'IRange.AdvancedFilter xlFilterCopy, CRange, ORange, True

' Determine how many unique rows we have

LastRow = Cells(Rows.Count, NextCol + 2).End(xlUp).Row

' Sort the data

Cells(1, NextCol + 2).Resize(LastRow, 1).Sort Key1:=Cells(1, _

NextCol + 2), Order1:=xlAscending, Header:=xlYes

End Sub

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 R537. 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 as the result of a formula

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

Click here to view code image

.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

Click here to view code image

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

Click here to view code image

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.

Using formula-based conditions to return above-average records

The formula-based conditions formula criteria are cool but are a rarely used feature in a rarely used function. Some interesting business applications use this technique. For example, this criteria formula would find all the above-average rows in the data set:

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

Using filter in place in Advanced Filter

It is possible to filter a large data set in place. In this case, you do not need an output range. You normally specify a criteria range; otherwise, you return 100% of the records, and there is no need to use the advanced filter!

In the user interface of Excel, running Filter In Place makes sense: You can easily peruse the filtered list, looking for something in particular.

Running a filter in place in VBA is a little less convenient. The only good way to programmatically peruse the filtered records is to use the xlCellTypeVisible option of the SpecialCells method. In the Excel user interface, the equivalent action is to select Home, Find & Select, Go to Special. In the Go to Special dialog box, select Visible Cells Only.

To run a Filter In Place, use the constant XLFilterInPlace as the Action parameter in the AdvancedFilter command and remove the CopyToRange from the command:

Click here to view code image

IRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=CRange, _Unique:=False

Then you use this programmatic equivalent to looping by using Visible Cells Only:

Click here to view code image

For Each cell In Range("A2:A" & FinalRow).SpecialCells(xlCellTypeVisible)

Ctr = Ctr + 1

Next cell

MsgBox Ctr & " cells match the criteria"

If you know that there will be no blanks in the visible cells, you can eliminate the loop with this:

Click here to view code image

Ctr = Application.Counta(Range("A2:A" & _FinalRow).SpecialCells(xlCellTypeVisible))

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

Click here to view code image

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

The real workhorse: xlFilterCopy with all records rather than unique records only

The examples at the beginning of this chapter talk about using xlFilterCopy to get a unique list of values in a field. You used unique lists of customers, regions, and products to populate the list boxes in your report-specific userforms.

However, a more common scenario is to use an advanced filter to return all records that match the criteria. After the client selects which customer to report, an advanced filter can extract all records for that customer.

In all the examples in the following sections, you want to keep the Unique Records Only check box cleared. You do this in VBA by specifying Unique:=False as a parameter to the AdvancedFilter method. This is not difficult to do, and you have some powerful options. If you need only a subset of fields for a report, copy only those field headings to the output range. If you want to resequence the fields to appear exactly as you need them in the report, you can do this by changing the sequence of the headings in the output range.

The next sections walk you through three quick examples to show the options available.

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

Click here to view code image

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

Click here to view code image

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

Click here to view code image

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

Next steps

The techniques from this chapter give you many reporting techniques available via the arcane Advanced Filter tool. Chapter 12, “Using VBA to create pivot tables,” introduces the most powerful feature in Excel: the pivot table. The combination of advanced filters and pivot tables can help you create reporting tools that enable amazing applications.