• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

SUM using Excel 2007 VBA

Hi all.

I have a spreadsheet with 3 columns: Invoice, Date and Amount (columns A, B and C).

I would like to use the SUM function using code but I don't know how to do it. Also, the number of rows will vary everytime. Sometimes there will be 3 rows, other times there may be 20 rows.

The data begins in row 5. So, I would need to sum all the numbers starting with cell C5, but again, I don't know how many rows there will be.

The result should be in the row right after the last row having data. So if there are 5 rows, then the SUM would be in in (row 6 ) or C6.

Thank you in advance!
1 Solution
Sub Summr()
    Dim iRow As Long

    iRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Offset(0, 0).Row
    Range("C" & iRow + 1).Formula = "=SUM(" & Range("C5").Address & ":" & Range("C" & iRow).Address & ")"
End Sub

Open in new window

printmediaAuthor Commented:
Worked great! Thanks!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now