Summing a Range using VBA 1

[jpgactmod]

This sounds like a simple question, but I do not know how to sum a range of numbers in an Excel Worksheet within a VBA subroutine. For example, assume Cells A2:B4 contain the numbers 1,2,3,4,5, & 6. How do I set a variable, say, X within a VBA subroutine such that X equals the sum of all numbers in the range A2:B4 (i.e. in this example, X should = 21)??

Thank you for any help.

 

[Zygor]

Try something like this


Private Sub AddCells()
Dim intAddedCellValues As Integer

For inti = 1 To 6
intAddedCellValues = intAddedCellValues + Range("A" & inti).Value

Next inti
MsgBox intAddedCellValues
End Sub

 

[jpgactmod]

Zygor,

Thanks much for your suggestion. Actually, I have a very large matrix that needs to be added, and I was hoping that VBA might have a one-line command that would do this to avoid iteratively adding up one cell at a time.

Thanks again.

jpgactmod

 

[Zygor]

if you include

Application.ScreenUpdating = False

before the line "For Inti = 1 to 6"
and
Application.ScreenUpdating = True

after the line "Next IntI", it will take almost no time

 

[Zygor]

I just tested it with 34416 rows and it took less than 1 second

 

[jpgactmod]

Zygor,

Thanks much. This does help quite a bit.

jpgactmod

 

[PHV]

And what about something like this ?
Application.WorksheetFunction.Sum(Range("A2:B4"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jpgactmod]

PHV,

This is exactly what I was looking for! Works great, and I guess the same approach can be used for many (all?) other Excel functions.

Thank you very much!!

jpgactmod