# Thread: SUM ROWS (VB/VBA)

1. ## 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. ## 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>

3. ## 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. ## 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
•