Excel’s  AutoSum feature is very flexible and powerful. However, there are times when you may desire to create a macro to “automatically” enter the =SUM function.

Let’s assume the cells to sum are in a column. If the number of cells in the column changes, the sum macro must be flexible, adding all the cells in the range, even if the number of cells in the range change.

Recording the AutoSum in VBA gives undesirable results.

Try recording the AutoSum feature. Place data in cells D5:D15 and place the cursor in D16.

With Relative Referencing OFF, you get something like this:

 =SUM("D5:D15")

With Relative Referencing ON, you get something like this:

Selection.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"

As far as we are concerned, both of these are Absolute, they work only if the Sum range is always D5:D15.
The Solution:

To create the AutoSum, we will use the Relative recorded macro and modify it to be truly relative. The recorded macro will look like:

    Selection.FormulaR1C1= "=SUM(R[-10]C:R[-1]C)"

First, let’s review the recorded formula to fully understand what is going on. This is called the R1C1 format.

The R[-10]C is the same as referring to cell D5

Refer to the row 10 cells up in the current column from the active cell.

The R[-1]C is the same as referring to cell  D15

Refer to the row 1 cell up in the current column from the active cell.

To make it truly relative the  -10  must be replaced with a variable. We will always refer to the next cell up, R[-1], but the first cell in the range will usually be a different number of cells away from the activecell.

The final formula will look like this:

Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"

The  vDiff  is the variable, replacing the  -10  inside the  R[ ]

The outside quotes are required for the FormulaR1C1 VBA function, they are not actually entered into the cell.

The two inside quotes and the characters inside the quotes replace
the -10

This is a concatenated formula. In English, this is what happens:

“=SUM[R”   the first text string, then add to it

vDiff   (the ampersand ( & ) is the symbol for adding text strings together..

Then add to it

]C:R[-1]C)
Now that the formula is written, the variable (the number of rows in the sum range) needs to be computed. In the completed macro, this computation will be done first, then the formula will be entered.

In other words, the macro must compute the number of rows in the column of data and place that in number in the variable vDiff

We will assume that the data is always in the same location on the spread sheet, therefore, the top row of the data to be summed will always be the same.

We need to add more code to the macro

vTopRow = 5

The first cell of data is always in row five

The last row to be summed is the cell immediately above the active cell

vRowBottom = ActiveCell.Offset(-1, 0).Row

This variable now holds the row number, in this example 15

Subtract the two variables and add one to get the number of rows to be added.

So the macro now reads:

Sub EnterAutoSum ()

vRowTop = 5
vRowBottom = ActiveCell.Offset(-1, 0).Row
vDiff = vRowBottom – vRowTop + 1
Selection.FormulaR1C1 = “=SUM(R[” & -vDiff & “]C:R[-1]C)”

End Sub
The macro, as written here, will now “AutoSum” the column for you each time the macro is run. Simply copy this code into your module and you will be read to go.
Enhancing the Code

Some users have requested to have the macro place the cursor in the correct cell for them. To enhance the macro to do this, you want to ensure the cursor is always in the next blank cell under the data.

There are several other steps that can be added to make this macro work with little action on your part. It is up to you on how many such features or steps you add to make it work best for you.

This is the completed macro used in several files where we have recently assisted clients. This example contains three rows of data and the cursor moves automatically to the =SUM cell in each column:

Sub EnterAutoSum ()

    'To position the cursor in the "AutoSum" cell
Range("Summary").Offset(1, 3).End(xlDown).Offset(1, 0).Select

    A cell at the top of the work area has been named "Summary"

    'Determine the Row numbers
vRowTop = 5
vRowBottom = ActiveCell.Offset(-1, 0).Row

    'Compute the R[ ] variable
vDiff = vRowBottom - vRowTop + 1

    'Enter the formulas
Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"

     'Move the cursor one cell to the right
Selection.Offset(0, 1).Select

      'To enter the =Sum formula in the second column of data
Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"

      'Move the cursor one cell to the right
Selection.Offset(0, 1).Select

      'To enter the =Sum formula in the third column of data
Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"

End Sub

At first, the logic in this macro is a bit confusing. But after studying the logic for a short while, the logic begins to fall into place. Once you understand the formula and how the macro works with the variable, you then must add the the practical commands to the code. In this example, the macro automatically finds the column of data and places the cursor in the proper cell. It the enters the formula in the next two columns as well.

Obviously the little extra work added to this macro makes the steps easier on you. But how much additional work you want the macro to do is totally up to you and your style of working.

This macro should work in all versions of Excel since Version 5.

This exercise is written assuming the user has the basic skills to record, run and edit VBA code