Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
Do you need a macro? Are you adding column AF data ?
Daniel 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
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 -- Gary''s Student - gsnu200858 "aussiegirlone" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
Yes I need a macro. AF already has the subtotals
"aussiegirlone" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
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 "Daniel.C" wrote: Do you need a macro? Are you adding column AF data ? Daniel 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200858 "aussiegirlone" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200858 "aussiegirlone" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
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" wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200858 "aussiegirlone" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True
But, what's the use of EndRow ? Daniel 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" wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200858 "aussiegirlone" wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
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. "Daniel.C" wrote: Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True But, what's the use of EndRow ? Daniel 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" wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200858 "aussiegirlone" wrote: 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting a row below last row of data to Sum total
I dont 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 "Daniel.C" wrote: Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True But, what's the use of EndRow ? Daniel 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" wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200858 "aussiegirlone" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting rows and adding to total worksheet | Excel Discussion (Misc queries) | |||
locate data from a chart by inserting data into a cell | Excel Worksheet Functions | |||
lock a total row below a list & allow inserting in the list? | Excel Worksheet Functions | |||
Inserting Copied Row To Always Insert Above Sub Total | Excel Worksheet Functions | |||
Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions |