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

	B	C	D	E	F
Row 8	Alphabet	$	$	$	$
Row 9	Alphabet	$	$	$	$
Row 10	Alphabet	$	$	$	$
Row 11	Alphabet	$	$	$	$
Row 12	Alphabet	$	$	$	$
Row X	(want to insert "Total" at the last row)	(want to sum up from C8 all the way to last row)	(want to sum up from D8 all the way to last row)	(want to sum up from E8 all the way to last row)	(want to sum up from F8 all the way to last row)

<tbody>
</tbody>

<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