Excel VBA Tutorial: Learn To Use VBA To Overcome Macro Recorder Limitations

Reposted from

If you record a macro where you sort your data, the macro records the last contiguous row in the data set. Next time you run the macro, if you have more rows of data the recorded macro wont correctly sort to the new last row. Also, anytime you record a macro to sort data, the macro records the name of the sheet where you sorted the data. If your data appears on a sheet with a different name, the macro won’t work.

In our previous blog tutorial I instructed how to dim a variable to capture change (see http://www.vbaclass.com/blog/excel-vba-class-free-vba-tutorial-variables/).

In this lesson I will explain how to use Excel Visual Basic Applications to find the last row of data and concatenate a variable into a recorded macro.

To follow along, download this sample workbook.

Many VBA blogs recommend what I consider an ineffective method of locating the last row of data. Those blogs recommend you use the Goto > Special > Last Cell command.

Goto Command (Keyboard shortcut is Ctrl+G)

Excel Goto Command

Goto Dialog – Special Button

Excel Goto Special

Goto Special Dialog

Goto Special Last Cell

In VBA, the code to achieve that command is


However theres a potential problem with using that command. If you have deleted rows or columns, this brings you to the end of the deleted data, not the active data. Once data is placed in a cell the cells status changes from clean to dirty. The Goto > Special > Last Cell command may bring you past the end of the actual data.

If you try Goto > Special > Last cell on the Sheet1 worksheet tab in the workbook you downloaded (link) notice you end up in an empty cell. There used to be data there, but it was deleted. The Goto > Special > Last Cell command isn’t aware of this condition.

A more reliable method is, assuming you know that you have data starting in column A, go to the bottom of the spreadsheet, like cell A1000000. From cell A1000000 press ctrl+up arrow key to jump to the last row of data.

In VBA I would code this as follows:

Dim lastrow

In this code, the lastrow variable stores the row of the activecell, the last cell in column A that contains a value.

Now that youve located the last row you can concatenate your lastrow variable into any Excel macro.

In Excel, the concatenation operator is the & symbol. You could use the & concatenation operator in a formula as follows:

Concatenate Operator

In VBA the concatenation operator works in a similar fashion with variables.

In the workbook you’ve downloaded previously (link) Sheet1 has 86 rows of data, while Sheet2 has 89 rows of data. I recorded a macro to sort the data on Sheet1 by State (column C). Here is the code generated by sorting the data.

Sub Macro1()
Range(“C3”).Select ‘I right clicked on cell C3 to access the sort command
‘*******************All of this code is a sort command in VBA************* ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear ‘Problem with the sheet name
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Sheet1”).Sort
.SetRange Range(“A1:H86”) ‘Here is where the macro recorder hard-codes row 86
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
‘******************End of the Data Sort command***************************
End Sub

Now, if you try to run the macro on Sheet2, it fails for two reasons. First, the recorded macro hard-codes the name of the sheet three times. Even if we fix the sheet name issue, it will still only sort to row 86, but Sheet2 has 89 rows of data.

To fix this problem we need to substitute the activesheet object for the code that reads Worksheets(“Sheet1”). In the updated code shown below, i have commented out the bad lines and marked where i used the activesheet object. The second issue is the last row as Range(“A1:H86”), that’ where i’ll concatenate the lastrow variable. The edited macro appears below

Sub Macro2()

‘*************Add Code To Find The Last Row*************
Dim Lastrow
Lastrow = ActiveCell.Row
‘*************End of Code To Find The Last Row*************


‘*************Commented Out Recorded Macro Code*************
‘ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
‘With ActiveWorkbook.Worksheets(“Sheet1”).Sort

‘*************Code using Activesheet Instead*************
ActiveSheet.Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort

‘*************Concatenate Last Row Here*************
‘.SetRange Range(“A1:H86”) ‘I commented out the recorded macro code here
.SetRange Range(“A1:H” & Lastrow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
End Sub

I hope you will find this free Microsoft Excel VBA Tutorial useful. If you have questions or comments please contact me for assistance. If you have other topics you recommend I should blog about with Excel VBA please feel free to make suggestions.

To contact Excel Class Training about Excel VBA Classes visit our contact page here

PEMDAS and the Mathematical Order of Operations with Microsoft Excel

Microsoft Excel works similarly to using a calculator but there are important differences. On a calculator to perform a calculation you might type numbers, then math symbols like plus (+) or minus (-) and then you would press the equal key to complete your calculation.In Excel we don’t end with the equal sign, we begin with the equal sign. Additionally, you need to be aware of the mathematical order of operations, commonly associated with the acronym PEMDAS (Please Excuse My Dear Aunt Sally).

What the order of operations theorem states is that parenthesis () overrides exponentiation (^) which has a higher precedence than multiplication (*) or division (/) and that multiplication and division have a higher order of precedence than addition (+) and subtraction (-). For those of us who don’t have degrees in rocket science, exponentiation is the term used to say raise one number to the power of another number, like 28 which would equal 256 (2*2 eight times).

To see how PEMDAS applies to real world examples let’s look at two formulas:

Example of PEMDAS:

= 2 + 3 * 4

Most people would read that formula from left to right and calculate 2+3 equals 5 and 5 times 4 equals 20, and it wouldn’t be entirely wrong. However, because multiplication has a higher order of precedence than addition, Excel will first calculate 3*4 equals 12 and 12+2 equals 14.

Therefore, if you want the formula to produce 20 instead of 14 you need to override the order of operations by grouping the 2+3 terms inside parentheses as in:

= (2 + 3) * 4

In Excel formulas it is common to see many many parentheses. That is because most functions (predefined formulas) use parentheses to enclose the arguments (inputs) and those arguments may nest other functions in parentheses and each function may require parenthesis around the inputs. For example,


What is important to realize about using parentheses is that if you use () symbols you have to be careful to balance every open parentheses with a close parenthesis or the formula will produce an error. If you’re unsure of why this formula uses $ signs in the cell references, please refer back to our blog about absolute versus relative cell references.

To learn more about our Excel Training in New York City contact us here.

SumProduct Function Applications

SumProduct Function Applications

When teaching Excel classes students occasionally ask about the SumProduct function. There are many aspects of SumProduct that mirror the SumIfs functions capabilities but SumProduct can produce different answers when combined with the unary operator. This tutorial is not meant to be a thorough discussion of SumProduct but is meant to provide an addendum to class notes.

Click Here To Download A Sample Workbook

I have provided a picture of the worksheet for those who cannot open Excel workbooks:

SumProduct – Example 1

The SumProduct function multiplies one array by another array. An array is typically a range of cells in a single column. In the first example imagine that our goal is to calculate the total gross pay for all employees in all divisions and departments. The ‘old fashioned’ Excel approach is to write a formula to calculate hours * rate for each employee (cells G2:G21) then Sum those values. In this Level 1 example, we would need to generate 20 formulas in 20 cells to find the answer, where a SumProduct function calculates the result with less effort.

With SumProduct

Without SumProduct

SumProduct – Example 2

About fifteen years ago Excel did not offer a SumIfs function and SumProduct was an alternative to using an array formula. Like the SumIfs function, SumProduct can work conditionally using criteria. Where SumIfs only allows AND criteria, SumProduct also supports OR criteria (but this generally double counts and generates incorrect results). Several studies have shown that SumIfs functions calculate significantly faster than SumProduct functions.

In example 2 imagine that we want to only sum the gross pay if the division equals Maine and the department equals Sales. In this example the asterisk (*) between Array1 and Array2 operates as an AND operator. The formula reads, If column C contains “Maine” AND Column D contains “Sales”, then Sum the value in column E times column F. If we use a plus (+) instead of an asterisk, this represents an OR criteria but double counts the products of Maine and Sales.

With SumProduct

With SumIfs

Array Formula Sum If

SumProduct – Example 3

At times we see a double hyphen (- -) in a formula. In Excel the double hyphen is known as a unary operator. The purpose of the unary operator is to force text values to 0/1 values where zero means false and one means true.

SumProduct Formula

Like example 2 except now the – operator converts values where a cell contains Maine or Sales to 0 for false and 1 for true, then uses the corresponding 1 values to determine which values in columns E and F are multiplied and summed.

SumProduct is also used to compare values in two columns to count how many words or numbers match (typically columns in separate workbooks). In general, do not sort the data, this technique compares corresponding values within the same row.

SumProduct Formula

This formula asks Excel to compare the values in column C to the values in column I for the word Maine. The unary operator converts results to 0 for false or 1 for true, and then the SumProduct function sums the values for 1/true.

SumProduct can also compare the value in two columns to count identical values

SumProduct Formula

Like the previous example, this formula asks SumProduct to sum the 1 values if the numbers in column E and K are identical and equal 40.

SumProduct – Example 4

The most valuable use of the SumProduct is to calculate a weighted average (this question only occurs about once every ten years). The straight average sums the values and divides by the count. A weighted average considers some values have a higher weight. In this example imagine the weighted average is based on the count of Divisions, where there are six Connecticut values, four Maine values, and five New Hampshire and five Vermont values.

In this example the SumProduct weights the average gross pay based on the count of divisions.

SumProduct Formula – Longhand notation for weighted average

SumProduct Formula – Shorthand notation for weighted average