Programming in Microsoft Excel – Macros – Part 4


Back in the first part of this series on programming in Microsoft Excel, I took an inventory list consisting of a repeating set of three rows that held the product ID, the product name, and the quantity of the product on hand.  I showed how easily you could write a simple macro to rearrange this data into rows with each row representing a single product and each of three columns representing one of the three product attributes.  This macro worked great except for one little problem.  You had to press the shortcut key to execute the macro one time for each product as you worked your way down the list.  If your product list only contained a couple of dozen items, you might not care too much.  However, if you work for a large organization with hundreds if not thousands of items in the product list, continuing to press the shortcut key combination for each item not only takes time, but such repeated action would probably result in a sore wrist at best.

So what can we do to make this task easier?  The answer to anyone who has done any programming at all is: Create a loop.

The obvious problem is how do you determine the number of rows to use.  Let’s assume that you do not know how many products are in the list.  You could begin by using the Worksheet.Cells property which returns a range of cells.  For example if we were to use Cells(5,1), we would get the first five cells in column 1 (or A) from the current worksheet.  (If you do not begin the expression with Worksheet with the name of worksheet as a parameter, Excel assumes you mean the current worksheet.)  However, we do not want to hardcode the number of rows.  Another object of the worksheet is Rows which represents all of the rows in the worksheet.  A property of Rows is: Count.  Therefore, Rows.Count returns the number of rows in the spreadsheet.  In Excel 2010, a spreadsheet has  1,048,576 rows.  Therefore, Rows.Count would return this number.  Combined with Cells as in Cells(Rows.Count,1) we now have a range object consisting of all of the rows in column 1.

Ok, still not quite want you want yet.  You need to understand that the focus cell in the range object is now down at row 1,048,576.  How do we get it back up to the last row that contains data.  Using the keyboard, you could simply press CTRL-UpArrow.  In Excel VBA, you can accomplish the same thing by using End(xlUp).  When appended to the end of the Cells reference from the previous paragraph, you get Cells(Rows.Count,1).End(xlUp).  This effectively brings our range of cells back down to only the data in our product inventory list.

Finally, if we tack on the object property: .Row, we can return the row number that currently has focus which should be the last row in the range of inventory data.  Therefore, our entire expression to find the last row of data in our product list is: Cells(Rows.Count,1).End(xlUp).Row.

Ok, that was the hard part.  Honestly.  The rest is easy.  So we can generate a loop like the following:

LastRow = Cells(Rows.Count,1).End(xlUp).Row
For iCnt = 2 to LastRow
<Insert macro created in part 1 here to restructure the data>
Next iCnt

While this would work, you would process a lot of blank rows.  Why?  Remember that after each product is restructured to a single line, you finish the macro by deleting those two lines that previously held the product name and quantity and thus reduce the length of the table by 2.  An easy way to account for this is to add the following line just before the Next iCnt statement to manually increment our counter variable to adjust for this change.  (Ok, it sounds a little backwards because you might think why not just decrease the variable LastRow by those two rows.  Alas, that will not work, so our only real option is to increment the counter by 2.)

iCnt = iCnt + 2

(Another option that would work is to immediately divide LastRow by 3 in the program line that calculates it and before we use it to define our loop.)

Therefore, our final macro code would look like the following after our edits.

Now if we save this code change to our macro, we can go back to an unmodified version of our product list, position our cursor in the first row of data, and execute the shortcut to execute our macro one time.  The macro now runs repeatedly until it reaches the end of the data restructuring all of the rows.

You now have seen that you can modify a previously recorded macro, save the changes, and execute the new macro with the new functionality.  Could you have written the entire macro directly in the editor?  I suppose so, but here is my take on building macros.  Use the macro recorder to your advantage to capture most of the functionality that you want from the macro.  Then go back into the macro to customize the code to meet your needs.  I’ve always been a proponent of using wizards to get the basic functionality of whatever it is you are building such as forms, reports, and even macros like this.   But don’t depend on the wizard to do everything.  Keep your options open by manually customizing the code the wizard generates.

Next time we will look at creating user defined functions (UDFs) that can perform actions not available from the existing built-in Excel functions.

See you next time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s