# sum a dynamic range macro

#### daveyc18

##### Well-known Member
Rich (BB code):
`````` Rows("6:6").Select
Selection.AutoFilter
ActiveSheet.Range("\$A\$6:\$Q\$65000").AutoFilter Field:=3, Criteria1:="OPEN"
ActiveSheet.Range("\$A\$6:\$Q\$65000").AutoFilter Field:=2, Criteria1:="abc"
Columns("D:K").Select
Selection.EntireColumn.Hidden = True

Range("L20").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-13]C:R[-1]C)"
Range("L20").Select
Selection.AutoFill Destination:=Range("L20:N20"), Type:=xlFillDefault
Range("L20:N20").Select``````

So the problem is the range won't always be "L20" -- it could be lower or higher. Any way to make so that it's dynamic? If it helps to have a reference point, cell "L6" will always have the header "Collateral Value."

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hello,

how is 'L20' or the cell cell that is should be, determined?

Hello,

how is 'L20' or the cell cell that is should be, determined?

it's the bottom cell summing the numbers in column L above

it's the bottom cell summing the numbers in column L above

Possibly something like the code below if you are summing from L7 to the last cell in column L....

Code:
``````Sub SumColL()
Dim lrow As Long
lrow = Cells(Rows.Count, "L").End(xlUp).Row
Cells(lrow + 1, "L").Formula = "=SUM(l7:l" & lrow & ")"
Cells(lrow + 1, "L").AutoFill Destination:=Cells(lrow + 1, "L").Resize(1, 3)
End Sub``````

Replies
12
Views
708
Replies
0
Views
483
Replies
1
Views
2K
Replies
5
Views
832
Replies
2
Views
677

### Forum statistics

1,203,728
Messages
6,057,012
Members
444,902
Latest member
ExerciseInFutility

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