SUM using Excel 2007 VBA

Posted on 2012-09-19
Last Modified: 2012-09-19
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!
Question by:printmedia
    LVL 13

    Accepted 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


    Author Closing Comment

    Worked great! Thanks!

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now