VBA sum variable range with some empty rows between variable range and sum
<p>I did some research and got the feeling its a rather simple question..</p>

<p>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</p>

<p><a href="https://i.stack.imgur.com/ORHAF.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ORHAF.jpg" alt="enter image description here"></a></p>

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

<p>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:</p>

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

<p>Got the feeling it should be something like this:</p>

<pre><code>Sub Sumvariablerow
  Range("L40").Select
  ActiveCell.FormulaR1C1 = "=SUM(L25:L" &amp; R - 3 &amp; ")"
End Sub
</code></pre>

<p>found the following topics:
<a href="https://stackoverflow.com/questions/28990296/how-to-sum-values-in-variable-range-in-vba">How to sum values in variable range in VBA?</a></p>

<p><a href="https://stackoverflow.com/questions/25085074/vba-summing-a-column-of-variable-length">VBA Summing a Column of Variable Length</a></p>

<p><a href="http://www.mrexcel.com/forum/excel-questions/584966-visual-basic-applications-sum-variable-length-column.html" rel="nofollow noreferrer">http://www.mrexcel.com/forum/excel-questions/584966-visual-basic-applications-sum-variable-length-column.html</a></p>

<p><a href="http://www.mrexcel.com/forum/excel-questions/251264-sum-formula-variable-rowcount-using-visual-basic-applications.html" rel="nofollow noreferrer">http://www.mrexcel.com/forum/excel-questions/251264-sum-formula-variable-rowcount-using-visual-basic-applications.html</a></p>

<p>thanks in advance</p>