# Sum of a dynamic range in VBA, How to:

#### KniteMare

##### Board Regular
ActiveSheet.Range("E2").value = Sum(ActiveSheet.Range(Cells(st, colStart), Cells(dn, colStart)))

The above "Does Not Work", but should give you an idea of what I am trying to do.

What I want to do is get the total of this range during the code execution so that I can place it in Cell E2.

If I use the Formula Method I get a #NAME? error.

Yours in EXCELent Frustration

KniteMare

PS. I found a great Animated.GIF (I saved it in my Private Drive, as the author does not want hyperlinking to his site to utilize these.)I want to use in my signature, how do I go about placing it there? PM With answer as It would seem to be a bit outside the normal disscussion group topics. Tnx in advance Knite.
This message was edited by KniteMare on 2002-09-20 12:23

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Maybe something like this might help?

Range("E2").Value = WorksheetFunction.Sum(Range("A2", Range("A2").End(xlDown)))

That is how I derived the variables that I use to define the range, viv a vis the Range(Cells(....

Like this:

colStart = 2
nm = ActiveSheet.Name
st = Cells(1, colStart).End(xlDown).Row
dn = Cells(200, colStart).End(xlUp).Row
Set myRange = ActiveSheet.Range _
(Cells(st, colStart), Cells(dn, colStart))

What I need to do is have Cell E2 = the sum of the target range above offset by 0,1

I messed with the Help files to no avail. I figured I'd post here before I try a different approach.

Yours in EXCELent Frustration

KniteMare

GOT IT!!

Thanks Tom You were right after all!!

Yours in slightly less EXCELent Frustration

KniteMare

Replies
10
Views
508
Replies
1
Views
1K
Replies
4
Views
443
Replies
6
Views
2K
Replies
0
Views
645

1,207,257
Messages
6,077,337
Members
446,278
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back