Excel.Tips.Net ExcelTips (Menu Interface)

Using SUM In a Macro

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Using SUM In a Macro.

Bob has a need to use the SUM function in a macro in order to find the sum of all the values in a column. The problem is that the number of cells to be summed will vary; for one run of the macro it could be 100 cells, while on the next it could be 300 and on the third only 25.

First, it is easy to use most worksheet functions (such as SUM) from within a macro. All you need to do is to preface the function name with "Application.WorksheetFunction." or simply "WorksheetFunction." Thus, if you know that each run of the macro will require summing A1:A100, then A1:A300, and finally A1:A25, you could use a macro like this:

Public Sub Sum_Demo()
    Dim myRange
    Dim Results
    Dim Run As Long

    For Run = 1 To 3
        Select Case Run
        Case 1
            myRange = Worksheets("Sheet1").Range("A1", "A100")
        Case 2
            myRange = Worksheets("Sheet1").Range("A1", "A300")
        Case 3
            myRange = Worksheets("Sheet1").Range("A1", "A25")
        End Select
        Results = WorksheetFunction.Sum(myRange)
        Range("B" & Run) = Results
    Next Run
End Sub

This macro uses a For . . . Next loop to specify different ranges of cells to be summed. It then uses the SUM worksheet function to assign the sum to the Results variable, which is (finally) stuffed into a cell in column B. The results of the first run are put in B1, the second in B2, and the third in B3.

While this particular macro may not be that useful, it shows several helpful techniques, such as how to define a named range, how to use the SUM function, and how to stuff the sum into a cell. What the macro doesn't do is to show how to select a variable number of cells to be summed. To do this, it is best to rely upon the End method of the Range object. The following code line shows how you can stuff the sum of the range starting at A1 and extending to just before the first blank cell in the column:

myRange = ActiveSheet.Range("A1", Range("A1").End(xlDown))
Range("B1") = WorksheetFunction.Sum(myRange)

Note that a range (myRange) is defined as beginning with A1 and extending through whatever the End method returns. This is then summed and stuffed into B1.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3217) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Using SUM In a Macro.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Dave    15 Nov 2016, 06:23

You could adapt the code that I wrote and posted in June 2015. Assign the value of Retention to the variable 'r' and away you go!

Adjust the formula for Column and starting Row to suit your needs.
Emmanuel    14 Nov 2016, 11:42
Hello there,

is there a macro that will sum up a variable number of cells in a table (from 1 to 5 depending on another cell Retention)?
Thanks in advance.
Barry    14 Oct 2016, 07:41

I would suggest that you just use the formula option in conditional formatting. This will save you from the issues surrounding the use of macros.
Dave    13 Oct 2016, 05:57

Here is a very basic macro that will do what you need. It assumes that the data is in the range A1:C7. You will need to change the For statement to read your data. Don't be afraid to experiment!

Sub applycolour()
    Dim i As Integer
    For i = 1 To 7
        If Cells(i, 3) < Cells(i, 1) + Cells(i, 2) Then
            Cells(i, 3).Interior.Color = vbYellow
        End If
    Next i
End Sub
Rajkumar    13 Oct 2016, 01:34
i have an issue, kindly solve it.

have three columns ar A, B, and C.

issue is C should be in yellow color if C< A+B.

How will write in macros.
kindly help
Dave    06 Oct 2016, 07:20
@Alan Legg

The following macro does what you want, but there is one proviso: You MUST put the text "end of list" (without quotes) at the bottom of the column of numbers. You may leave a space or two if you wish. If you prefer a different end-of-list identifier, then make sure to modify the code to match.

I hope you find this to be useful. There may be more elegant solutions, but at least I know that this one works!


Sub InsertSubTotals()
    Dim i As Integer ' main loop counter
    Dim j As Integer ' inner loop counter
    Dim iRow As Integer ' initial number of rows in the list of numbers
    Dim Tot As Integer ' subtotal value to be written
    iRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
    Tot = 0
    i = 1
    knt = 0
    Do Until Range("D" & i).Value = "end of list"
        If WorksheetFunction.IsNumber(Range("D" & i).Value) Then
            For j = i To iRow
                If WorksheetFunction.IsNumber(Range("D" & j).Value) Then
                    Tot = Tot + Range("D" & j).Value
                    Range("D" & i).Select
                    Selection.Insert shift:=xlDown
                    Range("D" & i).Value = Tot
                    Tot = 0
                    iRow = iRow + 1
                    i = j
                    Exit For
                End If
            Next j
        End If
        i = i + 1
End Sub

Alan Legg    05 Oct 2016, 20:38
Can you write a macro to do the following all within a given column (say D): Starting at row 1, each time you encounter a cell containing a number (ex. D45) to sum all contiguous numbers until you encounter a blank cell (ex.D45:D48); then place that sum into the cell just above the first cell of that group (ex. D44). Note: the number of contiguous cells in each group will vary.
Dave    05 Oct 2016, 02:45

VBA by its very nature is programming, so activity is mostly controlled within a loop, such as For, While, Do Until and so on. If you only have a single function to perform, a formula will normally suffice.

If I understand your question and example correctly, you could have VBA place an addition formula of the type that you suggest at the bottom of a column of data and copy it across as many columns as you need, but the simplest method is probably the SUM formula inserted manually rather than by a macro.
JOHN    22 Sep 2016, 15:50


A1:A3 = 1 1 1
B1:B3 = 1 1 1

C1:C3 = 2 2 2


Dave    25 Feb 2016, 10:58
Try the following code:

    Dim i As Integer
    Dim irow As Integer
    irow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To irow
        Cells(i, 1) = Cells(i, 1) * 100 / 114
    Next i

You will need to add a command button and assign the macro to it. It is'nt the refresh button but it's still only one click.

If you don't like Cells(i, 1), you can always substitute it with Range("A" & i) if you prefer.
Endy    23 Feb 2016, 07:03
Hi there,

Is there a way to write a macro that can calculate columns in this order:

Column A (Price * 100 / 114), so whenever i open the sheet and click refresh it automatically calculate all values in Column A, multiply the values found in Column A by 100 and divide it by 114?
Dave    29 Jun 2015, 03:30
... or this if you prefer!

    Dim r As Integer
    r = Range("B1").Value
    Range("B3").Value = WorksheetFunction.Sum(Range("A1:A" & r))
Dave    29 Jun 2015, 03:28
@ ulysis

Here are three lines of code that should do the trick. What the code does is:
a) define an integer variable
b) assign the value of cell B1 to the variable
c) SUM the contents of column A from A1 to the selected row and put the answer into the cell of your choice (in this case B3)

    Dim r As Integer
    r = Range("B1").Value
    Range("B3").Value = WorksheetFunction.Sum(Range("A1:A" & Range("B1").Value))
ulysis    28 Jun 2015, 03:58
thanks for your useful tips..
but, i need vba to sumup certain rows based on a cell value
i.e. if cell b1 contains 6 then i want vba to sum cell a1 to a6 and if cell b1 contains 9 then vba to sum cell a1 to a9.

please help me on this

thanks in advance
Jeremy    03 Jun 2015, 08:54
Holy wow Dave, talk about being the exact right thing I needed. Thanks so much man.
nader    04 May 2015, 16:39
Thank you so much Dave for your helpful comment
AStepToInfinity    27 Apr 2015, 02:30
I just needed a small sample about makro programming. So thank you for this helpful, short and compact example. very good!
Dave    06 Jan 2015, 03:57
Here's an alternative if you want to add SUM to a cell when you don't know how many rows of data your macro will meet during any given run.

Assuming you want to add the values in Column D of your data, and put the total in the next row below, use the following two lines of code:

Range("D" & (x + 1)).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & knt & "]C:R[-1]C)"

This may look a little complicated to anyone not familiar with macros, so let's break it down into its components to make it easier to understand.

Variables x and knt are Integer (or Long if you prefer).
x is the loop counter, so it holds the current Row number.
knt is a count of the number of rows of data processed, which allows for your data not starting at Row 1. You must choose your own method of assigning a value to knt.

The formula uses the R1C1 convention to define the range to be summed. The range is defined as the current Row (R) minus the number of records processed for the current Column (C), down to the current Row (R) minus 1 for the current Column (C).

In other words, it starts at the selected cell and sums the cells from the one immediately above back up along the column for as many rows as were processed.

If you prefer to have a blank row between the data and the SUM cell, then select the cell given by (x+2) and change the portion of the SUM formula to R[-2] to match.

After the macro has run, you will see the SUM formula in the usual format in the cell, such as =SUM(D4:D127) .

This works nicely for me. I hope it helps you too.

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2017 Sharon Parq Associates, Inc.