Sum Columns:R1C1 notation

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

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" <jkend69315@aol.com> wrote in message
news:1153142518.762572.236490@s13g2000cwa.googlegroups.com...
> 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
>

Thanks, Bob. Interesting. Will study. James
Bob Phillips wrote:
> 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" <jkend69315@aol.com> wrote in message
> news:1153142518.762572.236490@s13g2000cwa.googlegroups.com...
> > 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
> >