Have sum total at the bottom of a column regardless of the number of rows

[natemoss1218]

Hi all,

I am not sure if this is possible the way I want it, but figured it was at least worth picking the much smarter brains in this forum.

I want to put a sum total at the bottom of a column. But, the number of rows in that column will change daily. Lets say it's in column A. One day there may be 20 entries in column A in which case I would want the word "Total" in row 21 and the actual sum in row 22 of column A. The next day it may be 7 entries in which case I would want the word "Total" in row 8 and the actual sum in row 9. For this use case, pivot tables would not work due to formatting and a slew of other reasons. Additionally, this needs to be fully automated - meaning coming back to this daily and summing the rows isn't an option.

Again, I know this probably isn't doable, but figured I would ask!

Thanks in advance

 

[mohadin]

hI
WHAT ABOUT

Sub test()
    Dim LR As Long
    Dim x
    LR = Cells(Rows.Count, 1).End(xlUp).Row - 1
        x = Application.Transpose(Sheets("sheet1").Cells(2, 1).Resize(LR))
   Cells(LR + 1, 1) = "TOTAL"
    Cells(LR + 2, 1) = WorksheetFunction.Sum(x)
End Sub

 

[Fluff]

Another option

Sub natemoss()
   With Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)
      .Offset(1).Value = "Total"
      .Offset(2).FormulaR1C1 = "=sum(r1c:r[-2]c)"
   End With
End Sub

 

[natemoss1218]

Both of these work, thank you both for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[mohadin]

Dito