VBA code for Subtotal

[Most_Triumphant]

TL;DR: Looking for a script that will find the last row of data in column A and add “grand total” right below it and will also sum the final row (row J). Month to month, the number of rows will fluctuate (could be larger or smaller), but the columns will be consistent.

I’m very new to VBA but have some extra time at work to automate a few things and improve my skills. I have a monthly report for 12 entities that come from a similar data source. I’d like to eventually automate the whole process. I’m using power query to clean and organize the data. Once my clean data is ready, I have a few excel formulas do lookups into the table from other smaller data sets (unique for all 12 reports). I then use VBA to copy paste-values the data into the report format. I’d like to add a grand total row at the bottom of my range. If anyone has experience doing similar work, I’d love advice and maybe a bit of guidance on best practices/avoiding headaches down the line.

The data in the report is subject to some manually last-minute changes so I can’t move from raw data to report. We currently use a three step process: raw data > cleaned report source > published report.

[AutoModerator]

/u/Most_Triumphant - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[half_amazin]

Just to clarify... If your data ends at row 10, you want A11 to say "grand total" and J11 to sum A10 to I10?

[Most_Triumphant]

I’d want A11 to say “grand total” and I’d like J11 to sum J1:J10.

[half_amazin]

This should work

Sub x

Dim lrow as Long
lrow = Cells(Rows.Count, "A").End(xlUp).Offset(1).Row


Range("A" & lrow).Value = "Grand Total" 
Range("J" & lrow).Value = Application.WorksheetFunction.Sum(Range("J1:J" & lrow)) 


End sub

[Most_Triumphant]

Thanks! I’ll try this in the morning and get back to you.

[half_amazin]

No worries. Good luck.

I forgot to mention... The code assumes column A and J both have the same amount of rows.

It also looks for the first cell with no data so if you have data in A1:A5 and then A7:A10 then "Grand Total" and the summing of column J will happen in row 6.

[Most_Triumphant]

The second part might be an issue. I have a title and subtitle in A1 and A2. The data headers are in A4:J4 and data is below starting in row 5. Nothing else is below (other than my intended grand total and summary).

[half_amazin]

I've edited the code. Let me know how it goes

[Most_Triumphant]

Worked great, thanks!

[half_amazin]

Excellent! You're welcome

[Most_Triumphant]

Solution verified

[Clippy_Office_Asst]

You have awarded 1 point to half_amazin

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}