Excel strategy: make your code more useful

Excel’s CurrentRegion command can be used to create dynamic ranges in a macro for an advanced filter, making your code more useful and flexible.

QUESTION

I’ve recorded a macro to apply an advanced filter but it has fixed ranges. How do I make it flexible so that it handles different sized ranges?

ANSWER 

When you record a macro, Excel enters the ranges into the macro code. This means if the range changes you have to edit the range in the code. Also, the ranges don’t update if rows or columns are inserted or deleted on the sheet. 

This is one of the limitations of recorded macros. To get around this limitation you can edit the recorded code to make it flexible.

I covered Excel’s Advanced Filter feature in my July 2012 article (for those of you who have told me that you cut out my articles and keep them in a file). Advanced filters allow you to apply complex filters that are impossible to achieve with the built-in filter drop-down options. Figure 1 shows a typical layout used to apply advanced filters.

Figure 1The top section is the criteria section. The orange cells are for input and are used to create the filters. The criteria section needs to have the exact column headings used in the data table. The bottom section is the data table to be filtered. You don’t have to list all the column headings but it is commonly done. 

The filter criteria shown in Figure 1, when applied, will display all males and females with the middle initial K. This filter is impossible to achieve with Excel’s built-in drop-down filters. If you apply the Gender (male) filter and then select the MiddleInitial (K) filter, only males with a middle initial K will be displayed. Because K is on a separate row to male, it works as described. If K were on the same row as male, it would only show males with the middle initial K, just like a normal filter. Entries on separate rows mean one criteria or another criteria, in our case, male or K. 

An important part of this technique is that there is a blank row separating the criteria and the data: row 10 in Figure 1.

Once you set up the structure it still takes a few clicks to create or reapply an advanced filter, which makes it an ideal candidate for a macro. (However, remember that running a macro clears the whole undo list. This means you can’t undo anything after running a macro.)

Let’s record a macro to apply the advanced filter.

When applying advanced filters it is best to select a cell within the data before you start. That way, Excel will automatically guess the correct data range to filter.

1. Select cell A11.

 Figure 22. Press the following four keys in sequence (don’t hold them down): Alt T M R. Doing this will start the macro recorder. The macro recorder watches what you do and writes code to match your actions. This code can then be “replayed” to perform the actions again. 

3. In the Macro Name box, type Adv_Filter. You can’t have spaces in macro names. Click in the Shortcut Key box and hold down the Shift key and press A. This will allow you to use Ctrl + Shift + A to run the macro. In the Store Macro In drop-down, make sure the This Workbook option is selected and click OK. See Figure 2. 

4. Click the Data ribbon tab, then click the Advanced icon in the middle of the ribbon. The correct range for the data should have already been entered in the List Range box. Click in the Criteria Range box and then scroll to the top of the sheet and select the range A1:E3 and click OK. See Figure 3. The filter is applied (note the blue row numbers; these mean a filter is in place). 

 Figure 35. Press in sequence Alt T M R to stop the macro recorder. 

Always remember to stop recording your macros, otherwise you end up with a lot of extra code.

Test the macro by changing the K to a J. Hold the Ctrl and Shift keys down and press A. All males and only those females with a middle initial J should be visible. This worked because the ranges hadn’t changed.

Let’s review the macro that was created. (See Figure 4.) You can see that the ranges in Figure 3 are coded into the macro. If the data expands or you add more criteria to the rows below row 3, then the macro won’t work properly.

There are two changes we can make to the code to handle changing ranges – see Figure 5.

Figure 4Range(“A11:E1011”) has been changed to Range(“A11”).CurrentRegion 

Range(“A1:E3”) has been changed to Range(“A1”).CurrentRegion

Adding the CurrentRegion command to the top left cell reference of the range lets you use Excel’s built-in technique of selecting tables. If you select A1 and press Ctrl + * (I use the * on the numeric keypad), then Excel will select all the cells in the range until it encounters a blank row and a blank column. Try it and see. It is called the current region and Ctrl + * is the shortcut key. Use Ctrl + Shift + 8 if your laptop doesn’t have a numeric keypad. 

 Figure 5The CurrentRegion command creates dynamic ranges by forcing Excel to find the current region for each range, every time the macro is run. 

This technique assumes no blank rows within the criteria range or the data table. A blank row within the advanced filter criteria range instructs Excel to display all the data. It is Excel best practice to have no blank rows and no blank columns within data tables. This technique also assumes that the column on the right of the data table is blank – column F in Figure 1. 

If you use that same structure in other sheets with the criteria starting in A1 and the data starting in A11, then you can use the same macro to apply the advanced filter.

Test the amended code by adding a K in cell D4 under the J in D3, which I had changed in instruction 5 in the previous column. This filter should display males plus females with the middle initials J or K. 

Professional Development: Data search, data validation, and macros in Excel 2013: Master the skills of data search, validation and macros in Excel 2013.

Recording macros with flexibility 

When Excel uses a dialog box to capture ranges it always enters the range into the recorded macro. If you are just selecting a range to format or copy, you can use a keyboard shortcut to select a flexible range. As an example, we can record a macro to apply the red font to the whole data table. The macro we create will work on any size of table, as long as you select a cell in the table before running the macro. 

Figure 61. Select cell A11.

2. Press in sequence Alt T M R.

3. Type Red_Font in the Name box and make sure This Workbook is showing in the Store Macro In drop-down. Click in the Shortcut Key box and the hold the Shift key down and press R. Click OK.

4. Press Ctrl + * and then apply the red font colour.

5. Stop the macro using Alt T M R.

The code created is shown in Figure 6. Notice the CurrentRegion command is used. I will demonstrate a more common way to start the macro recorder on the companion video.

Filter shortcuts

A filter shortcut I use frequently is Alt A C pressed in sequence. This removes all applied filters. Ctrl + Shift + L is a toggle and will add or remove the filter drop-downs. You need to have a cell selected within the table before using the shortcut. 

The companion video and an Excel file (blank and completed) may assist your understanding.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]


Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.
May 2017
May 2017

Read the May issue

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

CONTENTS