Sum used in macro where range stops at first non numerical cell

[Carol]

Is there anyway to include in a macro a sum up to the first non numerical
cell? Or in ohter words the size or name of the range varies each time you
use the macro.

 

[OssieMac]

Hi Carol,

Try the following. Note that blank cells evaluate as zero and are therefore
numeric and hense the testing for numeric as well as for blanks.

Sub SumNumeric()

Dim iFirst As Long
Dim iLast As Long
Dim sumTotal

iFirst = 2
iLast = iFirst

'Tests cells for numeric and not blank
'in column A starting at row 2
Do While IsNumeric(Cells(iLast, "A")) _
And Cells(iLast, "A") <> ""

iLast = iLast + 1
Loop

'iLast will be one greater than required number
'because it increments after the test
'so reduce value by 1
iLast = iLast - 1

sumTotal = WorksheetFunction _
.Sum(Range(Cells(iFirst, "A"), _
Cells(iLast, "A")))

End Sub