Sum dynamic number of rows using vba

[jerryneubauer]

Sometimes the easiest problem is hard to solve.

I have a report that returns a dynamic number of rows depending on the volume of transactions.
I already have code that takes me to the first blank cell in Col. "F":

Range("A" & Rows.Count).End(xlUp).Offset(1, 5).Select

Now i need to basically perform an AutoSum from F5 to the last dynamic row in Col. "F".

Thanks

 

[James006]

Hello,

You could test following :

Sub Test()
MsgBox WorksheetFunction.Sum(Range("F5:F" & Cells(Application.Rows.Count, 6).End(xlUp).Row))
End Sub

HTH

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG10Mar17
[COLOR="Navy"]With[/COLOR] Range("A5", Range("A" & Rows.Count).End(xlUp))
    Range("F" & .Count + 5) = Application.Sum(.Offset(, 5))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jerryneubauer]

Thanks to all for the assist. After all your comments, and more research, I came up with this:

Const SourceRange = "F:F"
Dim NumRange As Range, formulaCell As Range
Dim SumAddr As String
Dim c As Long
For Each NumRange In Columns(SourceRange).SpecialCells(xlConstants, xlNumbers).Areas
SumAddr = NumRange.Address(False, False)
Set formulaCell = NumRange.Offset(NumRange.Count, 0).Resize(1, 1)
formulaCell.Formula = "=SUM(" & SumAddr & ")"
c = NumRange.Count
Next NumRange

It works.
Thanks to all for the help.

 

[DanMor]

I have a very similar set of code to this (especially the last 6 lines) that works for my case. see below for code.
I have a couple of questions to add on to this.
Q1) each time it goes through loop I would like the final sumaddr formula to format the result to 2 decimal places.
Q2) I also would like to add other formulas in the same row (which is dynamic) that may or may not have anything to do directly with this loop. for example same row different column (G) would truncate the number, or in another column (P) to populate with totally unrelated formula that starts another action.

any help or direction would be appreciated.

Thanks

For Each NumRange In Columns("C").SpecialCells(xlConstants, xlNumbers).Areas
SumAddr = NumRange.Address(False)
NumRange.Offset(NumRange.Count, 0).Resize(1, 1).Formula = "=SUM(" & SumAddr & ")"
C = NumRange.Count

Next NumRange