Excel VBA Sum Range Of Cells

Excel VBA Sum of Range Banner
The math checks out here…

Summing values is pretty simple in Excel with a spreadsheet and the SUM() function. You just set a cell to have a formula, ‘=SUM([Range]),’ and you can sum the values in any Range. But how can you the same in VBA?

It took me a bit of digging to figure this one one, surprisingly. I wanted to know if VBA had the same method, where you could specify a range and get the entire sum for the cell’s in that Range.

I was not having any luck at first, so I thought I was going to have to iterate over the Range, cell by cell, and sum all the values manually. But then I thought, “How can normal do this better than VBA?” Turns out, I finally found my answer in the form of Worksheet functions!

Worksheet functions are extremely powerful, and anyone familiar with Excel spreadsheets will know a lot o these functions once they see them. Let’s take a look at the SUM() function!


Say you had a few numbers in some cells:

Excel VBA Sum of Range 1
Before running the macro

Create a macro and paste the following code:

Sub VBASum()
Worksheets("Sheet1").Cells(1, 4).Value = WorksheetFunction.Sum(Range("A1:C1"))
End Sub

This will write the sum of the cells in the 4th cell of the first row. Run the macro and:

Excel VBA Sum of Range 2
After running the macro

Voila! Just like the SUM() function in normal Excel. Simply pass in a Range as the parameter, and you can get your sum in one line.

Leave a Reply

%d bloggers like this: