Sum a dynamic range of cells based on another cells value

Hi,

Trying to sum a range of cells across Excel spreadsheets and using VBA to do it.

The length of the range changes in each cell but the range will always be the same, i.e. using column A - values starting in row 2. Formula in B2 is sum(A2:A3002), B3=sum(A3:A3003) and so on if range was 3000 cells - this can change based on a value entered elsewhere (if this is the easiest way to do it). This formula would continue until the first cell found is blank (i.e. the end of the range).

Also if it is possible i would simply like to return the values rather than the formulas as this task in itself makes my Excel fall over....

Something about turning off screen refresh i have heard....

Thanks in advance for your help.

place the cursor and run the macro

Hello,

Just place the cursor on the first cell of the sum in this example you should select cell A2 or A3 and run this macro:

Option Explicit

Sub SumSpecificRange()
'
' SumSpecificRange Macro

Dim sum As Variant

sum = 0

Do Until ActiveCell.Value = ""

sum = sum + ActiveCell.Value
ActiveCell.Offset(1, 0).Select

Loop

Dim findrange As Variant

findrange = sum

Do Until findrange = 0

findrange = findrange - ActiveCell.Value
ActiveCell.Offset(-1, 0).Select

Loop

ActiveCell.Offset(1, 1).Value = sum

End Sub

It will sum until the first empty cell and will print the value on cell b2 or b3 depending if you are running for based on cell a2 or a3.

I hope it helps.
Cátia Santos