Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Location
    Kansas, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUM ROWS (VB/VBA)

    I need to sum rows in a spreadsheet. Someone had posted the following code to sum columns and it works great, but I cannot seem to figure out how to edit the code to sum rows. My number of columns and rows to total changes daily. Thanks for your help!

    Sub QuickTotals()
    r = ActiveCell.CurrentRegion.Rows.Count
    Set SumRow = ActiveCell.CurrentRegion.Offset(r, 0).Resize(1)
    SumRow.FormulaR1C1 = "=SUM(R[" & -r & "]C:R[-1]C)"
    End Sub

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: SUM ROWS (VB/VBA)

    Can you try this
    <pre>Sub QuickTotals2()
    Dim iCols As Integer, iRows As Integer
    With ActiveCell.CurrentRegion
    iCols = .Columns.Count
    iRows = .Rows.Count
    Set SumCol = .Offset(0, iCols).Resize(iRows, 1)
    End With
    SumCol.FormulaR1C1 = "=SUM(RC[" & -iCols & "]:RC[-1])"
    End Sub</pre>

    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM ROWS (VB/VBA)

    Thanks!

    <pre>Sub SumCols()
    Dim sumRow
    Dim iCols As Integer, iRows As Integer
    With ActiveCell.CurrentRegion
    iCols = .Columns.Count
    iRows = .Rows.Count
    Set sumRow = .Offset(iRows, 0).Resize(1, iCols)
    End With
    Dim str1 As String
    str1 = "=SUM(R[" & -iRows & "]c:R[-1]c)"
    sumRow.FormulaR1C1 = str1
    End Sub</pre>


    Exercise for the reader:
    Now develop a function that will sum a two-dimensional range of cells and place the overall sum off the bottom right-hand corner.
    Hint: "=SUM(R[" & -iRows & "]c[" & -iCols & "]:R[-1]c[-1])"

  4. #4
    New Lounger
    Join Date
    Sep 2004
    Location
    Kansas, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM ROWS (VB/VBA)

    Thanks! This is what I needed...works like a charm!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •