Add a Total Row to the bottom where the number of rows will be a variable

[gheyman]

I have data starting from Row 8. The Last row # will be different each time.

After I run my code, which puts formulas down Column F I want to find the last row and in the next one put a formula that sums column F (Column F values will always start in Row 8)

So If I have data running from F8 down to F26 (which will always change - could be F32 the next time) I want to put a formula in F27 that sums From F8 down to F26.

Is this Possible?

 

[njimack]

Dim LastRow As Long

LastRow = Cells(Rows.Count, 6).End(xlUp).Row


Cells(LastRow, 6).Offset(1).Formula = "=SUM(F8:F" & LastRow & ")"

 

[gheyman]

Thanks My friend!

What if I wanted to format that cell with a double underline? Is it possible to format?

 

[njimack]

Try this:

Dim LastRow As Long

LastRow = Cells(Rows.Count, 6).End(xlUp).Row


With Cells(LastRow, 6).Offset(1)
    .Formula = "=SUM(F8:F" & LastRow & ")"
    .Borders(xlEdgeBottom).LineStyle = xlDouble
End With

 

[gheyman]

Cool! Thanks. I appreciate the help!

 

[gheyman]

What if I wanted a Sumif instead of Sum?

LastRow = Cells(Rows.Count, 2).End(xlUp).Row
'
With Cells(LastRow, 6).Offset(1)
.Formula = "=SUMIF(C8:C" & LastRow & ","Child",F8:F" & LastRow & ")"
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeTop).LineStyle = xlContinuous
End With

Obviously, it doesn't like my formula.

 

[njimack]

Try changing this:

.Formula = "=SUMIF(C8:C" & LastRow & ","Child",F8:F" & LastRow & ")"

to this:

.Formula = "=SUMIF(C8:C" & LastRow & ", ""Child"" ,F8:F" & LastRow & ")"

 

[gheyman]

Sorry for delayed response. That worked perfectly.

THANK YOU