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 **

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 **