Sum Columns

[Zone]

Another post got me interested in this. It's desired to put the sum at
the bottom of columns C through E. I want to put the sum there, not a
formula. I thought some kind of R1C1 notation would be necessary, but
I got this method from help. The notation seems odd but it works well.
Is this the best way to do it?
TIA, James

Sub SumCols()
Dim k As Integer, LastRow As Long, myRg As Range
For k = 3 To 5
LastRow = Cells(65536, k).End(xlUp).Row
Set myRg = Range(Cells(2, k), Cells(LastRow, k))
Cells(LastRow + 1, k) = Application.WorksheetFunction.Sum(myRg)
Next k
End Sub

 

[Bob Phillips]

Nothing wring with that per se.

You shouldn't hard-code the number of rows though, and you could also use
Resize

Sub SumCols()
Dim k As Integer, LastRow As Long, myRg As Range
For k = 3 To 5
LastRow = Cells(Rows.Count, k).End(xlUp).Row
Set myRg = Cells(2, k).Resize(LastRow -1)
Cells(LastRow + 1, k) = Application.WorksheetFunction.Sum(myRg)
Next k
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Zone]

Thanks, Bob. Interesting. Will study. James

Nothing wring with that per se.

You shouldn't hard-code the number of rows though, and you could also use
Resize

Sub SumCols()
Dim k As Integer, LastRow As Long, myRg As Range
For k = 3 To 5
LastRow = Cells(Rows.Count, k).End(xlUp).Row
Set myRg = Cells(2, k).Resize(LastRow -1)
Cells(LastRow + 1, k) = Application.WorksheetFunction.Sum(myRg)
Next k
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)