Help Needed for Using VBA to Sum Totals till last row for Multiple Columns

[vbanewlearner]

Hi,

Q1: I have the following simplified chart (note: row can change so want to be able to do the totals at the last row). Total line want to make sure no decimals, bolded the entire Total row)

Q2: If want to add in column G that will return value % with 1 decimal which take column F $ divided by column D $ from row 8 to last row including total line

[TABLE="width: 1060"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Row 8[/TD]
[TD]Alphabet[/TD]
[TD]$[/TD]
[TD]$[/TD]
[TD]$[/TD]
[TD]$[/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD]Alphabet[/TD]
[TD]$[/TD]
[TD]$[/TD]
[TD]$[/TD]
[TD]$[/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD]Alphabet[/TD]
[TD]$[/TD]
[TD]$[/TD]
[TD]$[/TD]
[TD]$[/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD]Alphabet[/TD]
[TD]$[/TD]
[TD]$[/TD]
[TD]$[/TD]
[TD]$[/TD]
[/TR]
[TR]
[TD]Row 12[/TD]
[TD]Alphabet[/TD]
[TD]$[/TD]
[TD]$[/TD]
[TD]$[/TD]
[TD]$[/TD]
[/TR]
[TR]
[TD]Row X[/TD]
[TD](want to insert "Total" at the last row)[/TD]
[TD](want to sum up from C8 all the way to last row)[/TD]
[TD](want to sum up from D8 all the way to last row)[/TD]
[TD](want to sum up from E8 all the way to last row)[/TD]
[TD](want to sum up from F8 all the way to last row)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>Thank you.

 

[AFPathfinder]

The important bit of VBA is finding the last row in your table:

finalRow = Cells(Rows.Count, 1).End(xlUp).Row

To place the formula in the next row down, just add a "+ 1" to the end of it:

finalRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

Then, just insert the formula into the first empty cell at the bottom of that column:

Range("B" & finalRow).Value = "=SUM(B2:B" & finalRow - 1 & ")"

For your second question, Just use relative referencing and add the formula to the entire row at once:

Range("B2:B" & finalRow - 1).FormulaR1C1 = "=RC[-1]/RC[-3]"

If you want the percent format:

Range("B2:B" & finalRow - 1).Style = "Percent"

 

[vbanewlearner]

Thank you so much for your help. It is really cleared what I should do. I will try it out