sum a dynamic range macro

[daveyc18]

 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."

 

[onlyadrafter]

Hello,

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

 

[daveyc18]

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

 

[MARK858]

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

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