Macro to Sum

[erock24]

I need a macro that will sum the values in column P from P8:?
The trick is that my # of rows will vary. I have the macro ending in the right spot. So, if I have 90 rows of data my macro ends in P91. Now I need to be able to have it sum P8:P90. Like wise if I have 300 rows of data my active cell will be P301. I need P8:P300 summed.

Is this possible?

Thank you for your time and help.

 

[VoG]

Try

Sub summ()
Dim lastrow As Long, total
lastrow = Cells(Rows.Count, 16).End(xlUp).Row
total = Application.Sum(Range("P8:P" & lastrow))
MsgBox total
End Sub

 

[al_b_cnu]

Hi,

Do you need it as a macro, or could you just do a formula:

=SUM(P:P)-SUM(P1:P8)

 

[erock24]

I don't think I know where to include it at. This is a sample of my code. There are things that happen before this, but the 1st 2 lines shown here get me to the end of P in the cell where an autosum would go. Then I tried your code there, but nothing. It didn't error out though, and it did the merge at the end. But no sum in P.

Range("P8").Select
    Selection.End(xlDown).Offset(1, 0).Select
    
Dim lastrow As Long, total
lastrow = Cells(Rows.Count, 16).End(xlUp).Row
total = Application.Sum(Range("P8:P" & lastrow))
    
    Range("A8").Select
    Selection.End(xlDown).Offset(1, 0).Select
    Range(Selection, Selection.Offset(0, 1)).Select
Selection.Merge

Application.ScreenUpdating = True
End Sub

 

[VoG]

Perhaps

Sub summ()
Dim lastrow As Long, total
lastrow = Cells(Rows.Count, 16).End(xlUp).Row
total = Application.Sum(Range("P8:P" & lastrow))
Range("P" & lastrow + 1).Value = total
Range("P" & lastrow + 1 & ":Q" & lastrow + 1).Merge
End Sub

 

[erock24]

Range("P8").Select
    Selection.End(xlDown).Offset(1, 0).Select
    Dim lastrow As Long, total
lastrow = Cells(Rows.Count, 16).End(xlUp).Row
total = Application.Sum(Range("P8:P" & lastrow))
Range("P" & lastrow + 1).Value = total

This is exactly what I needed. Thank you very much for your time and help.

 

[VoG]

I'm glad it worked but did you notice something? I didn't use Select at all - it is very rare to have to use Select and using it slows your code down.

 

[erock24]

thank you.

 

[erock24]

How could I edit your code to do the same sum but for columns D:P
In other words, Put the sum of D8:last row in the last row +1 of D, the sum of E:8:last row in the last row+1 E, etc.

Thanks for your help.

 

[VoG]

Try

Sub summ()
Dim lastrow As Long, icol As Integer, Total As Variant
For icol = 4 To 16
    lastrow = Cells(Rows.Count, icol).End(xlUp).Row
    Total = Application.Sum(Range(Cells(8, icol), Cells(lastrow, icol)))
    Cells(lastrow + 1, icol).Value = Total
Next icol
End Sub