Tip Use Variables to Avoid Hardcoding Rows and Formulas

The macro recorder never records a variable. They are very easy to use. They are discussed in more detail later, but just as in BASIC, a variable can remember a value.

My recommendation is to set the last row with data to a variable. I like to use meaningful variable names, so my favorite for this is FinalRow.

FinalRow = Range("A65536").End(xlUp).Row

Now that you know the row number of the last record, it is easy to put the word Total in Column A of the next row.

Range("A" & FinalRow + 1).Value = "Total"

■ For simpler methods of referring to this range, see "Using the Offset Property to Refer to a Range," p. 65,in Chapter 3.

You can even use the variable when building the formula. This formula totals everything from E2 to the FinalRow of E:

Range("E" & FinalRow + 1).Formula = "=SUM(E2:E" & FinalRow & ")"

0 0

Responses

  • summer
    How to avoid hard coding in vba?
    8 years ago

Post a comment