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

#### natemoss1218

##### New Member
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!

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
hI
VBA Code:
``````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``````

Another option
VBA Code:
``````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``````

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

Glad we could help & thanks for the feedback.

Dito

Replies
3
Views
139
Replies
3
Views
274
Replies
14
Views
372
Replies
9
Views
303
Replies
1
Views
39

1,203,239
Messages
6,054,314
Members
444,715
Latest member
GlitchHawk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back