Using VBA to total numbers in a column where the row will always vary

slivesay

Board Regular
Joined
Jan 4, 2019
Messages
64
I would like some help to see if I can total one column of numbers where the last row will always vary. The 1st row is headers (i.e. Order Number, Customer, Date Requested, etc), columns are A - Z & are constant. My header in J1 will be a word (qty) and the rest of the column will be numbers. Can I total the numbers via vba and put the answer in the cell after the last row in column J? I would like to do this w/ vba and using a command button.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
How about
Code:
Sub slivesay()
   Range("J" & Rows.Count).End(xlUp).Offset(1).FormulaR1C1 = "=sum(r2c:r[-1]c)"
End Sub
 
Upvote 0
Thank you! :) It works, but there is an issue I forgot to mention. The last row will always have data, but the cell in column J may not have data. If the cell in col J does not have data, it is putting the total in the first blank cell from bottom.
 
Last edited:
Upvote 0
Which column will always have data on the last row?
 
Upvote 0
Ok, try
Code:
Sub slivesay()
   Range("Y" & Rows.Count).End(xlUp).Offset(1, -15).FormulaR1C1 = "=sum(r2c:r[-1]c)"
End Sub
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Hello! I want to thank you again for all your assistance. I truly appreciate it.

May I ask, would it be hard to add code to this one so the data would be centered? It is set to 'right align' currently.
 
Upvote 0
Very easy :)
Code:
Sub slivesay()
   With Range("Y" & Rows.Count).End(xlUp).Offset(1, -15)
      .FormulaR1C1 = "=sum(r2c:r[-1]c)"
      .HorizontalAlignment = xlCenter
   End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top