Hi Mark,

Three options. first two basically the same but the first one finds the last
cell of the range first before assigning the entire range to a variable.

The second option does the above in one line of code.

Third option is just simple code if you actually know the range.

The space and underscore at the end of lines are line breaks in otherwise
single lines of code. (Just in case you are not aware of this)

Sub Sum_a_Range_Option1()

Dim dTotal As Long
Dim iRowCounter1 As Long
Dim iColumnCounter1 As Long
Dim rngToSum As Range
Dim rngLastCell As Range

dTotal = 0
iRowCounter1 = 2
iColumnCounter1 = 5

With Sheets("Futures")

'Assign last cell to a range variable
Set rngLastCell = .Cells(iRowCounter1, _
iColumnCounter1).End(xlDown).End(xlToRight)

'Assign the entire range to a range variable
Set rngToSum = Range(.Cells(iRowCounter1, _
iColumnCounter1), rngLastCell)

End With

dTotal = WorksheetFunction.Sum(rngToSum)

End Sub

Sub Sum_a_Range_Option2()

Dim dTotal As Long
Dim iRowCounter1 As Long
Dim iColumnCounter1 As Long
Dim rngToSum As Range

dTotal = 0
iRowCounter1 = 2
iColumnCounter1 = 5

With Sheets("Futures")

'Assign the entire range to a range variable
'using only one line of code
Set rngToSum = Range(.Cells(iRowCounter1, _
iColumnCounter1), .Cells(iRowCounter1, _
iColumnCounter1).End(xlDown).End(xlToRight))

End With

dTotal = WorksheetFunction.Sum(rngToSum)

End Sub

Sub Sum_a_Range_Option3()

Dim dTotal As Long

dTotal = WorksheetFunction.Sum(Sheets("Futures") _
.Range("E2:I15"))


End Sub


--
Regards,

OssieMac


"MarkS" wrote:

Hi,
I need to know the sum of a group of cells I us this piece of code to do
this job

dTotal = 0
iRowCounter1 = 2
iColumnCounter1 = 5
Do Until Sheets("Futures").Cells(iRowCounter1, iColumnCounter1).Value = ""
Do Until Sheets("Futures").Cells(iRowCounter1, iColumnCounter1).Value = ""
dTotal = dTotal + Sheets("Futures").Cells(iRowCounter1,
iColumnCounter1).Value
iRowCounter1 = iRowCounter1 + 1
Loop
iRowCounter1 = 2
iColumnCounter1 = iColumnCounter1 + 1
Loop

Does any one know how to do this, without looping through all the cells, as
it is always the same size

Thanks MarkS