Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing a range without making it active
Hi
I'm trying to sum the total of one column in a variable row length range starting from cell E49. After my other code has executed the active cell would normally be beneath the last cell in Col C (let's say C101 for example). How would I enter the formula in E101 that would total all the cells in Col E from E49 to the end of the column (in this instance E100) without (a) selecting cells in Col E to establish the top and bottom row numbers and (b) hard coding the column reference i.e "E" in the code? The extract of my current code is as follows: Dim SelRow1, SelRow2 as Integer Range("E49").Select Let SelRow1 = Selection.Row Selection.End(xlDown).Select Let SelRow2 = Selection.Row Selection.Offset(1, 0).Select Let Selection.Formula = "=sum(E" & SelRow1 & ":" & "E" & SelRow2 & ")" It works but I don't like hard coding the column reference as I want to be able to re-use the code without editing the Let Selection.Formula = line every time. Thanks for any suggestions. Garry Douglas ** Please Remove SPMOFF to Reply ** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing a range without making it active
Garry,
Maybe this will get you pointed in the right direction... '--------------------------------------------------------- Sub RevisedCode() 'Jim Cone - December 28, 2004 Dim rngData As Excel.Range Dim lngLastCol As Long Dim lngRow As Long 'Assumes last column with data is the one you want. lngLastCol = Cells.Find(what:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column 'Change "1" (start row) as needed - finds first row with data lngRow = Cells(1, lngLastCol).End(xlDown).Row 'Establish range with the data Set rngData = Range(Cells(lngRow, lngLastCol), _ Cells(lngRow, lngLastCol).End(xlDown)) 'Enter the formula, one row below the data. rngData(rngData.Rows.Count + 1).Formula = _ "= sum(" & rngData.Address(False, False) & ")" 'Clean up Set rngData = Nothing End Sub '---------------------------------------- Regards, Jim Cone San Francisco, USA "Garry Douglas" wrote in message ... Hi I'm trying to sum the total of one column in a variable row length range starting from cell E49. After my other code has executed the active cell would normally be beneath the last cell in Col C (let's say C101 for example). How would I enter the formula in E101 that would total all the cells in Col E from E49 to the end of the column (in this instance E100) without (a) selecting cells in Col E to establish the top and bottom row numbers and (b) hard coding the column reference i.e "E" in the code? The extract of my current code is as follows: Dim SelRow1, SelRow2 as Integer Range("E49").Select Let SelRow1 = Selection.Row Selection.End(xlDown).Select Let SelRow2 = Selection.Row Selection.Offset(1, 0).Select Let Selection.Formula = "=sum(E" & SelRow1 & ":" & "E" & SelRow2 & ")" It works but I don't like hard coding the column reference as I want to be able to re-use the code without editing the Let Selection.Formula = line every time. Thanks for any suggestions. Garry Douglas ** Please Remove SPMOFF to Reply ** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a specific row number active from a cell entry | Excel Discussion (Misc queries) | |||
Info from one cell making other cells active | Excel Worksheet Functions | |||
Making Hyperlinks Active In Entire Column | New Users to Excel | |||
Making an active cell the first one to appear on the spreadsheet | Excel Programming | |||
Saving an external file without making it active | Excel Programming |