VBA sum variable range with some empty rows between variable range and sum

Question

I did some research and got the feeling its a rather simple question..

i want to sum a variable range: in my sheet, L25 can run to infinity, tricky part (for me), it shouldn't SUM till the last row-1 but last row -3 as there is a 'Totals' text above the place where the sum should be

so totals should be summed in L40, which in different cases can be another L row.

I think it's not that hard, but as I am a beginner, I think I make a elementary mistake. To show my effort, I managed to get this:

Sub Sumvariablerow
  Range("L40").Select
  ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-3]C)"
End Sub

Got the feeling it should be something like this:

Sub Sumvariablerow
  Range("L40").Select
  ActiveCell.FormulaR1C1 = "=SUM(L25:L" & R - 3 & ")"
End Sub

found the following topics: How to sum values in variable range in VBA?

VBA Summing a Column of Variable Length

http://www.mrexcel.com/forum/excel-questions/584966-visual-basic-applications-sum-variable-length-column.html

http://www.mrexcel.com/forum/excel-questions/251264-sum-formula-variable-rowcount-using-visual-basic-applications.html

thanks in advance

both solutions worked for me! thanks for the help

bart1701
– bart1701

2016-04-28 14:09:05 +00:00
Commented Apr 28, 2016 at 14:09 — bart1701
– bart1701, Commented Apr 28, 2016 at 14:09

Karthick Gunasekaran · Accepted Answer · 2016-04-28 13:55:34Z

1

try with the below code

Sub test()
    Range("L" & Range("L25").End(xlDown).Row + 3).Formula = WorksheetFunction.Sum(Range("L25:L" & Range("L25").End(xlDown).Row))
End Sub

edited Apr 28, 2016 at 13:55

answered Apr 28, 2016 at 13:36

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bart1701 Over a year ago

Thanks, this almost works but has a different problem: row sum is variable but it only counts (sums) the last row.. so (following the picutre) the sum according to your code is €365?

areklipno · Accepted Answer · 2016-04-28 13:17:25Z

1

Maybe something like that:

Range("L40").Formula = "=SUM(L25:L" & 24 + Range(Range("L25"), Range("L25").End(xlDown)).Rows.Count & ")"

answered Apr 28, 2016 at 13:17

areklipno

5285 silver badges12 bronze badges

1 Comment

bart1701 Over a year ago

Thanks, brings me somewhere, only problem, the "Range("L40").Formula =" causes the SUM will always be in the "L40" cell if list is longer (say totals should add up in L50, its still adding up @ L40? any thoughs?

Mrig · Accepted Answer · 2016-04-28 13:38:31Z

1

Try this:

Sub Sumvariablerow()
    Dim LastRow As Long
    LastRow = Range("L25").End(xlDown).Row
    Cells(LastRow + 2, 12).Value = "total"
    Cells(LastRow + 3, 12).Select
    ActiveCell.Formula = "=SUM(L25:" & ActiveCell.Offset(Rowoffset:=-3).Address & ")"
End Sub

edited Apr 28, 2016 at 13:38

answered Apr 28, 2016 at 13:25

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

2 Comments

bart1701 Over a year ago

thanks, same here, works but if there are less or more cells filled in L than it still places the sum in L40?

Mrig Over a year ago

@bart1701 - Its counting number of rows in the code. Oops I've edited my code.

Collectives™ on Stack Overflow

VBA sum variable range with some empty rows between variable range and sum

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related