Inserting a row below last row of data to Sum total

[aussiegirlone]

I would like to be able automatically insert a row below the last row of data
that would have the word “Total†displayed in the column AC and then
calculate the total Sum in column AF. Is this possible

 

[Daniel.C]

Do you need a macro? Are you adding column AF data ?
Daniel

 

[Gary''s Student]

Try this small macro or adapt it to your use:

Sub NewRow()
n = Cells(Rows.Count, "AF").End(xlUp).Row + 1
Cells(n, "AC").Value = "Total"
Cells(n, "AF").Formula = "=sum(AF1:AF" & n - 1 & ")"
End Sub



Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[aussiegirlone]

Yes I need a macro. AF already has the subtotals

 

[aussiegirlone]

Yes I need a macro to find the last row of data and then below that, insert a
row with the word "Total!" in the cell AC? and then sum all the Cells
above. The sheet has headers from A1 to AF1 and the column AF1 has the
subtotals. Are you adding column AF data ? Yes All columns have data in them

 

[aussiegirlone]

This macro is finding the first row of data below the header and then
inserting below it the row to total and then sum it. it is not finding the
last row of data below the header to insert the row total and then sum it

 

[Daniel.C]

Could you post a sample of your data ?
Daniel

This macro is finding the first row of data below the header and then
inserting below it the row to total and then sum it. it is not finding the
last row of data below the header to insert the row total and then sum it

 

[aussiegirlone]

I added this code below to the macro below and it now works properly?
EndRow = Cells(Rows.Count,1).End(xlup).row
The macro now looks like this.

Sub NewRow()
EndRow = Cells(Rows.Count,1).End(xlup).row
n = Cells(Rows.Count, "AF").End(xlUp).Row + 1
Cells(n, "AC").Value = "Total"
Cells(n, "AF").Formula = "=sum(AF1:AF" & n - 1 & ")"
End Sub

Thankyou for helping me
PS: There is one more thing I'd like to ask
Can the inserted row font be formatted bold at the same time?

 

[Daniel.C]

Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True
But, what's the use of EndRow ?
Daniel

 

[aussiegirlone]

I dont know What the use of EndRow dose to the formula, all I know is that it
now goes to the end of the last row of data found and your code completes the
insertion of text and summing. Without this line of code it was going to the
first row of data found. Anyhow, it works good so thanks again.

 

[aussiegirlone]

I don’t know what the use of endrow done to the code. All I know is that it
found the last row of data in the sheet instead of finding the first row of
data. And your code completed the insertion of text, summing and formatting
bold. So thankyou once again for your help