Recording macros in Excel is an easy way to automate repetitive tasks. Maybe you receive a new file every month with your sales figures in it, for example. You may need to sort the data, move it from one worksheet to another, or format it. If you repeat the same steps on the file month after month, this is a scenario where a macro will save you time and effort.
The problem comes when the file that you receive isn’t quite the same each month. It’s typical in this scenario for the incoming file to have a different number of rows each month. On a busy sales month, you might have 200 rows. On a slow month, maybe it’s only 50 rows. Your macro needs to be flexible enough to handle this.
There are a lot of ways to deal with this issue. In this article, I’m going to explain some simple things you can do to record macros that are flexible. We’ll look at the “Current Region” feature in Excel and the “Ctl” key sequences that you can use to move around your spreadsheet. I’ll also discuss the importance of staying within the boundaries of Excel while recording.
As you are recording your macro, keep in mind that everything you do gets recorded very literally in the macro. If, for example, you select the cells A1 through J12, the macro will always select cells A1 through J12. That’ll work fine, as long as your data is always exactly 12 rows. But if next month you’ve got 13 rows, the macro won’t work.
In the example above, you can create a more flexible macro by using Excel’s “Current Region” feature to select the range. This feature, which is found under the “Go To Special” option on the “Find and Select” button, will detect the cells that have data in them, based on the active cell. So if you’ve got data in columns A1 through J12, you would click on cell A1, and then use the “Current Region” feature. You’d end up with cells A1 through J12 selected. The end result won’t look any different from the Excel perspective, but the macro will. Here’s what the macro looks like when you manually select cells A1 through J12:
Sub SelectRange()
Range("A1:J12").Select
End Sub
As you can see, the columns and rows are explicitly called out in the macro. However, using the “Current Region” method instead, your macro will look like this:
Sub SelectRegion()
Range("A1").Select
Selection.CurrentRegion.Select
End Sub
As you can see, this method calls out only the starting cell. The ending cell is not specified. The “CurrentRegion.Select” method will automatically detect the end of the range. Your macro is now flexible enough to handle a variable number of rows and columns.
While recording your macro, there may be times when you need to move down to the last cell in a specific column, or to the last cell going across a row. In order to make your macro flexible, don’t click on the cell in question. Instead use one of the “ctl” key combinations: Ctl-Home, Ctl-End, Ctl-Right arrow, and Ctl-Down arrow (try each of these on one of your spreadsheets to see how they work, if you’re not familiar with them).
Let’s assume, for example, that your spreadsheet has data in cells A1 through J12. I recorded a macro that clicked on the first cell with data (A1), and then the last cell with data (J12). Here’s what it looks like:
Sub ClickLastCell()
Range(“A1”).Select
Range("J12").Select
End Sub
In order to get a more flexible macro, I recorded it again, this time using Ctl-End to get to the last cell instead of clicking on J12. Here’s what I got:
Sub CtlEndMethod()
Range(“A1”).Select
ActiveCell.SpecialCells(xlLastCell).Select
End Sub
Notice that this method does not call out cell J12. If I now add a few rows or columns to my data, my macro will still work perfectly.
We’ve looked at two methods for making your macros flexible, both of which involve carefully choosing the method you use to select various items on your sheet. Here’s one final tip for you to think about as you record macros. Once you start recording, you need to keep in mind that the macro recorder ONLY records things that happen in Excel. If you step out of Excel, for example, to open a different file, the macro recorder will not record those steps.
To illustrate, let’s assume that I’m still working with the Sales data discussed above (with data in cells A1 through J12), and I need to open a sheet that contains Sales Target data, and copy some information from that sheet to my Sales sheet. Once I start recording my macro, I’ll take the following steps:
Move to an empty cell at the bottom of my Sales sheet.
Open the Sales Targets sheet.
Select a set of data on the that sheet, and copy it.
Switch back to my Sales sheet and paste.
The potential problem here comes in step #2. As long as I stay in Excel and use the “Open” feature on the “File” tab, my macro will be correct. However, if I switch to File Explorer (or “My Computer”, or a folder, or the desktop) and double-click the Sales Target sheet to open it, the Open action won’t be recorded.
You can try this for yourself by following these steps:
Start the Macro recorder.
Switch to a folder ( or File Explorer or My Computer), and open an Excel file by double-clicking it.
Stop recording.
Now take a look at your macro. Here’s what I got when I did this:
Sub OpenTargetFile()
End Sub
That’s right, I got nothing. Not a single line of code. That’s because once I stepped out of Excel, the Macro Recorder was no longer aware of what I was doing. There are exceptions to this rule, and sometimes your macro will be OK. But other times you won’t be so lucky. To ensure that it’ll always record this action, be sure to use the “File” tab to open files.
As you’ve seen, you can greatly increase the flexibility and accuracy of your macros by thinking carefully about the steps you take while recording. You can make your macro work with any number of columns and rows by using the “Current Region” feature and the “Ctl” keyboard shortcuts. By staying within the boundaries of Excel using the “File” tab, you’ll ensure that all of the relevant actions get recorded in your macro.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© 2017 North Port Solutions, LLC. All right reserved.