Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Columns
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" wrote in message oups.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Columns
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" wrote in message oups.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make Columns A&B on Sheet2 always equal Sheet1 table columns A&B | Excel Worksheet Functions | |||
putting 2 long columns into multiple columns in excel page and sor | Excel Discussion (Misc queries) | |||
to convert columns to rows having mulit independent group columns | Excel Worksheet Functions | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) |