Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
Hello to all and Merry Christmas,
I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
Dim lastrow as Long, firstrow as Long Dim rng as Range lastrow = cells(rows.count,"B").End(xlup).row firstrow = cells(lastrow,"B").End(xlup).row Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"B").Formula = "=Max(" & rng.Address & ")" rng.CurrentRegion.Select application.Dialogs(xlDialogSort).Show Excel can sort on up to 3 columns at one time - so this just shows the sort dialog. -- Regards, Tom Ogilvy "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
Hi!
I hope that next code help you! Sub SumTheEnd() ' Select first row where are value, but not title Range("A4").Select ' change your own Selection.Name = "Start" Selection.End(xlDown).Select Selection.Name = "DynamicEnd" ' Selection two rows down ActiveCell.Offset(2, 0).Range("A1").Select ' Total's added (dynamic area) ActiveCell.Formula = "=SUM(Start:DynamicEnd)" End Sub Best regards Kari J Keinonen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
Tom Thank you, as usual I have another one or two questions.
1. If I have varing columns? some times I'll have 8, 10, 15 etc so they will vary for me. Do I need to set them? 2. Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Address & ")" I modified this to give me totals for column "C" with the formula maxa ().. Obviously it didn't work as it returned a zero, what do i need to change in your formula. I thought it would auto update the column automatically?? Thanks for your patience Bob "Tom Ogilvy" wrote in message ... Dim lastrow as Long, firstrow as Long Dim rng as Range lastrow = cells(rows.count,"B").End(xlup).row firstrow = cells(lastrow,"B").End(xlup).row Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"B").Formula = "=Max(" & rng.Address & ")" rng.CurrentRegion.Select application.Dialogs(xlDialogSort).Show Excel can sort on up to 3 columns at one time - so this just shows the sort dialog. -- Regards, Tom Ogilvy "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
Correct for word wrap
Sub thelastrowincolumns() lr = Cells.Find(what:="*", After:=Cells(1, 1), _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2 For i = 1 To 5 'your columns Cells(lr, i) = Application.Sum(Range(Cells(2, i), Cells(lr, i))) Next i End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Sub SumActiveColumn() ac = ActiveCell.Column lr = Cells.Find(what:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 Cells(lr, ac) = Application.Sum(Range(Cells(2, ac), Cells(lr, ac))) End Sub 'for all columns you specify same row for all Sub thelastrowincolumns() lr = Cells.Find(what:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2 For i = 1 To 5 'your columns Cells(lr, i) = Application.Sum(Range(Cells(2, i), Cells(lr, i))) Next i End Sub -- Don Guillett SalesAid Software "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
Sub thelastrowincolumns()
lr = Cells.Find(what:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2 For i = 1 To 5 'your columns 'Cells(lr, i) = Application.Sum(Range(Cells(2, i), Cells(lr, i))) 'Modify Toms to put max at bottom Set rng = Range(Cells(1, i), Cells(lr, i)) Cells(lr, i).Formula = "=Max(" & rng.Address & ")" Next i End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Sub SumActiveColumn() ac = ActiveCell.Column lr = Cells.Find(what:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 Cells(lr, ac) = Application.Sum(Range(Cells(2, ac), Cells(lr, ac))) End Sub 'for all columns you specify same row for all Sub thelastrowincolumns() lr = Cells.Find(what:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2 For i = 1 To 5 'your columns Cells(lr, i) = Application.Sum(Range(Cells(2, i), Cells(lr, i))) Next i End Sub -- Don Guillett SalesAid Software "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
rng refers to column B, so you would offset it one column as shown below.
Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Offset(0,1).Address & ")" If you want the sum, then Use Sum instead of Maxa. Current region should select all the columns that are contiguous. Click in your data and do Ctrl+Shift+8 this is the equivalent of the current region command. If that picks up all the data you want, then you shouldn't need to change. If it doesn't, then you might need to alter the code Assuming rng refers to the proper number of rows in column B, then rng.offset(0,-1).Resize(,20) would pick up columns A (the offset -1) for 20 columns as an example. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, as usual I have another one or two questions. 1. If I have varing columns? some times I'll have 8, 10, 15 etc so they will vary for me. Do I need to set them? 2. Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Address & ")" I modified this to give me totals for column "C" with the formula maxa ().. Obviously it didn't work as it returned a zero, what do i need to change in your formula. I thought it would auto update the column automatically?? Thanks for your patience Bob "Tom Ogilvy" wrote in message ... Dim lastrow as Long, firstrow as Long Dim rng as Range lastrow = cells(rows.count,"B").End(xlup).row firstrow = cells(lastrow,"B").End(xlup).row Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"B").Formula = "=Max(" & rng.Address & ")" rng.CurrentRegion.Select application.Dialogs(xlDialogSort).Show Excel can sort on up to 3 columns at one time - so this just shows the sort dialog. -- Regards, Tom Ogilvy "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
An additional question my wife brought up that needs to be performed on this
spreadsheet. In column A I may have 100 entries and many of them will be from the same ID number. I could have 25 different ID's there. Is there a way to selectively remove all but 4 and specifically request those 4 to be removed? That way when it was run it would return only the 4 I requested and then conduct the sort on them. Thanks so much BoB A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
Thanks again,
If I understand this correctly the rng is fixed and any other formula's that I want to put on that row would be as you indicated by the offset? Also the maxa is what I need as I'm looking for the largest number. Thanks BOB "Tom Ogilvy" wrote in message ... rng refers to column B, so you would offset it one column as shown below. Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Offset(0,1).Address & ")" If you want the sum, then Use Sum instead of Maxa. Current region should select all the columns that are contiguous. Click in your data and do Ctrl+Shift+8 this is the equivalent of the current region command. If that picks up all the data you want, then you shouldn't need to change. If it doesn't, then you might need to alter the code Assuming rng refers to the proper number of rows in column B, then rng.offset(0,-1).Resize(,20) would pick up columns A (the offset -1) for 20 columns as an example. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, as usual I have another one or two questions. 1. If I have varing columns? some times I'll have 8, 10, 15 etc so they will vary for me. Do I need to set them? 2. Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Address & ")" I modified this to give me totals for column "C" with the formula maxa ().. Obviously it didn't work as it returned a zero, what do i need to change in your formula. I thought it would auto update the column automatically?? Thanks for your patience Bob "Tom Ogilvy" wrote in message ... Dim lastrow as Long, firstrow as Long Dim rng as Range lastrow = cells(rows.count,"B").End(xlup).row firstrow = cells(lastrow,"B").End(xlup).row Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"B").Formula = "=Max(" & rng.Address & ")" rng.CurrentRegion.Select application.Dialogs(xlDialogSort).Show Excel can sort on up to 3 columns at one time - so this just shows the sort dialog. -- Regards, Tom Ogilvy "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
It's been interesting and quite a learning experience with all the comments,
thanks so much on a Christmas Monring. Still nagging is how to change the sum to a maxa and make it work in any of the programming. Additionally if there's a way to eliminate rows in the spreadsheet based on vba removing specific rows as I mentioned earlier..... The Sum won't work as I need to get the highest number in that column. I've tried to change it in all the formulas without success. Thanks BOB Reynolds "Don Guillett" wrote in message ... Sub SumActiveColumn() ac = ActiveCell.Column lr = Cells.Find(what:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 Cells(lr, ac) = Application.Sum(Range(Cells(2, ac), Cells(lr, ac))) End Sub 'for all columns you specify same row for all Sub thelastrowincolumns() lr = Cells.Find(what:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2 For i = 1 To 5 'your columns Cells(lr, i) = Application.Sum(Range(Cells(2, i), Cells(lr, i))) Next i End Sub -- Don Guillett SalesAid Software "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
Tom,
forget my other post about the maxa, since I hadn't completed looking through your response. The MaxA works fine and is doing exactly what I wanted it to. I tried the Ctrl-Shift-8 and it did what you said it would do and confirmed I had what I wanted selected.... Is there something I could put in there to automatically calculate the number of columns... rng.offset(0,-1).Resize(,20) make the 20 where it would calculate it???? "Tom Ogilvy" wrote in message ... rng refers to column B, so you would offset it one column as shown below. Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Offset(0,1).Address & ")" If you want the sum, then Use Sum instead of Maxa. Current region should select all the columns that are contiguous. Click in your data and do Ctrl+Shift+8 this is the equivalent of the current region command. If that picks up all the data you want, then you shouldn't need to change. If it doesn't, then you might need to alter the code Assuming rng refers to the proper number of rows in column B, then rng.offset(0,-1).Resize(,20) would pick up columns A (the offset -1) for 20 columns as an example. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, as usual I have another one or two questions. 1. If I have varing columns? some times I'll have 8, 10, 15 etc so they will vary for me. Do I need to set them? 2. Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Address & ")" I modified this to give me totals for column "C" with the formula maxa ().. Obviously it didn't work as it returned a zero, what do i need to change in your formula. I thought it would auto update the column automatically?? Thanks for your patience Bob "Tom Ogilvy" wrote in message ... Dim lastrow as Long, firstrow as Long Dim rng as Range lastrow = cells(rows.count,"B").End(xlup).row firstrow = cells(lastrow,"B").End(xlup).row Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"B").Formula = "=Max(" & rng.Address & ")" rng.CurrentRegion.Select application.Dialogs(xlDialogSort).Show Excel can sort on up to 3 columns at one time - so this just shows the sort dialog. -- Regards, Tom Ogilvy "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
cells(1,"IV").End(xltoLeft).Column
use a row that will give you the acurate count. -- Regards, Tom Ogilvy "rjr" wrote in message . .. Tom, forget my other post about the maxa, since I hadn't completed looking through your response. The MaxA works fine and is doing exactly what I wanted it to. I tried the Ctrl-Shift-8 and it did what you said it would do and confirmed I had what I wanted selected.... Is there something I could put in there to automatically calculate the number of columns... rng.offset(0,-1).Resize(,20) make the 20 where it would calculate it???? "Tom Ogilvy" wrote in message ... rng refers to column B, so you would offset it one column as shown below. Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Offset(0,1).Address & ")" If you want the sum, then Use Sum instead of Maxa. Current region should select all the columns that are contiguous. Click in your data and do Ctrl+Shift+8 this is the equivalent of the current region command. If that picks up all the data you want, then you shouldn't need to change. If it doesn't, then you might need to alter the code Assuming rng refers to the proper number of rows in column B, then rng.offset(0,-1).Resize(,20) would pick up columns A (the offset -1) for 20 columns as an example. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, as usual I have another one or two questions. 1. If I have varing columns? some times I'll have 8, 10, 15 etc so they will vary for me. Do I need to set them? 2. Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Address & ")" I modified this to give me totals for column "C" with the formula maxa ().. Obviously it didn't work as it returned a zero, what do i need to change in your formula. I thought it would auto update the column automatically?? Thanks for your patience Bob "Tom Ogilvy" wrote in message ... Dim lastrow as Long, firstrow as Long Dim rng as Range lastrow = cells(rows.count,"B").End(xlup).row firstrow = cells(lastrow,"B").End(xlup).row Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"B").Formula = "=Max(" & rng.Address & ")" rng.CurrentRegion.Select application.Dialogs(xlDialogSort).Show Excel can sort on up to 3 columns at one time - so this just shows the sort dialog. -- Regards, Tom Ogilvy "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
Thanks so much, got it and it works.
Wife sprung two more things on me All rows are equal width so the new part of the programming worked as you said; 1. I may have as many as 15 to 20 different groups with many entries each (lets say name of supervisor with many subordinates under) How can I get a sub total for each supervisor of how many subordinates there are under that person AND since I am only looking for 4 individual supervisors how can I eliminate all the other supervisors with their subordinates.? What she tells me she needs is a count of the same 4 supervisors all the time with a breakdown of a count of subordinates and the aging days for each as per the previous formula. This same programming will be used for 20 odd worksheets as they are imported. This should be the last, now that she has clarified it. Hope you can help, Thanks Bob Reynolds "Tom Ogilvy" wrote in message ... cells(1,"IV").End(xltoLeft).Column use a row that will give you the acurate count. -- Regards, Tom Ogilvy "rjr" wrote in message . .. Tom, forget my other post about the maxa, since I hadn't completed looking through your response. The MaxA works fine and is doing exactly what I wanted it to. I tried the Ctrl-Shift-8 and it did what you said it would do and confirmed I had what I wanted selected.... Is there something I could put in there to automatically calculate the number of columns... rng.offset(0,-1).Resize(,20) make the 20 where it would calculate it???? "Tom Ogilvy" wrote in message ... rng refers to column B, so you would offset it one column as shown below. Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Offset(0,1).Address & ")" If you want the sum, then Use Sum instead of Maxa. Current region should select all the columns that are contiguous. Click in your data and do Ctrl+Shift+8 this is the equivalent of the current region command. If that picks up all the data you want, then you shouldn't need to change. If it doesn't, then you might need to alter the code Assuming rng refers to the proper number of rows in column B, then rng.offset(0,-1).Resize(,20) would pick up columns A (the offset -1) for 20 columns as an example. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, as usual I have another one or two questions. 1. If I have varing columns? some times I'll have 8, 10, 15 etc so they will vary for me. Do I need to set them? 2. Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Address & ")" I modified this to give me totals for column "C" with the formula maxa ().. Obviously it didn't work as it returned a zero, what do i need to change in your formula. I thought it would auto update the column automatically?? Thanks for your patience Bob "Tom Ogilvy" wrote in message ... Dim lastrow as Long, firstrow as Long Dim rng as Range lastrow = cells(rows.count,"B").End(xlup).row firstrow = cells(lastrow,"B").End(xlup).row Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"B").Formula = "=Max(" & rng.Address & ")" rng.CurrentRegion.Select application.Dialogs(xlDialogSort).Show Excel can sort on up to 3 columns at one time - so this just shows the sort dialog. -- Regards, Tom Ogilvy "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a worksheet to find last row and add formula's
Obviously you can loop through all the data and delete rows that do not
refer to one of the 4 managers. You don't give any data layout, so I can't say what is the best way to do it. If you have a manager name on each row, then you can filter the data you want. Or even if you leave the data as is, you can use the countif and sumif worksheet functions. (but can also be used in VBA). Another approach would be to apply a pivot table to your data. This is easiest approach to summarizing data and doesn't really require programming unless you want to automate the application of the pivot tables. (found under the data menu). Debra Dalgleish has a lot of information about pivot tables at her site: http://www.contextures.com/tiptech.html Without knowing how you data is laid out, it is hard to say. I probably won't be around for a while, so you may want to post with more details as a new topic. -- Regards, Tom Ogilvy "rjr" wrote in message ... Thanks so much, got it and it works. Wife sprung two more things on me All rows are equal width so the new part of the programming worked as you said; 1. I may have as many as 15 to 20 different groups with many entries each (lets say name of supervisor with many subordinates under) How can I get a sub total for each supervisor of how many subordinates there are under that person AND since I am only looking for 4 individual supervisors how can I eliminate all the other supervisors with their subordinates.? What she tells me she needs is a count of the same 4 supervisors all the time with a breakdown of a count of subordinates and the aging days for each as per the previous formula. This same programming will be used for 20 odd worksheets as they are imported. This should be the last, now that she has clarified it. Hope you can help, Thanks Bob Reynolds "Tom Ogilvy" wrote in message ... cells(1,"IV").End(xltoLeft).Column use a row that will give you the acurate count. -- Regards, Tom Ogilvy "rjr" wrote in message . .. Tom, forget my other post about the maxa, since I hadn't completed looking through your response. The MaxA works fine and is doing exactly what I wanted it to. I tried the Ctrl-Shift-8 and it did what you said it would do and confirmed I had what I wanted selected.... Is there something I could put in there to automatically calculate the number of columns... rng.offset(0,-1).Resize(,20) make the 20 where it would calculate it???? "Tom Ogilvy" wrote in message ... rng refers to column B, so you would offset it one column as shown below. Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Offset(0,1).Address & ")" If you want the sum, then Use Sum instead of Maxa. Current region should select all the columns that are contiguous. Click in your data and do Ctrl+Shift+8 this is the equivalent of the current region command. If that picks up all the data you want, then you shouldn't need to change. If it doesn't, then you might need to alter the code Assuming rng refers to the proper number of rows in column B, then rng.offset(0,-1).Resize(,20) would pick up columns A (the offset -1) for 20 columns as an example. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, as usual I have another one or two questions. 1. If I have varing columns? some times I'll have 8, 10, 15 etc so they will vary for me. Do I need to set them? 2. Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Address & ")" I modified this to give me totals for column "C" with the formula maxa ().. Obviously it didn't work as it returned a zero, what do i need to change in your formula. I thought it would auto update the column automatically?? Thanks for your patience Bob "Tom Ogilvy" wrote in message ... Dim lastrow as Long, firstrow as Long Dim rng as Range lastrow = cells(rows.count,"B").End(xlup).row firstrow = cells(lastrow,"B").End(xlup).row Set rng = Range(" B" & Firstrow & ":B" & LastRow) Cells(lastrow + 2,"B").Formula = "=Max(" & rng.Address & ")" rng.CurrentRegion.Select application.Dialogs(xlDialogSort).Show Excel can sort on up to 3 columns at one time - so this just shows the sort dialog. -- Regards, Tom Ogilvy "rjr" wrote in message news:QoPjh.5351$_X.2614@bigfe9... Hello to all and Merry Christmas, I am responsible for downloading about 20 data sheets daily from the mainframe and they are saved in excel worksheet format on my desktop computer. Each download has a different amount of rows in it. Example: 1 may have 15 rows and 2 may have 200 rows, and 3 may have 25 rows, etc........... 1. My first problem is: How do I make excel recognize the very last row ? 2. If row 200 is the last entry, then I would want the total's added to row 202, leaving one space between the report and the totals. 3. The totals row would place my formula for each column in this row. i.e.: one formula would be to count the highest day out.. Column B would return 7 in the example. I also may have other formulas that might need to be entered so if someone wouldn't mind, show me an example of putting a different formula in the added formula for col B. 4. Then I need to sort the columns by one, two or even three columns. Is there a way to program a dialog box to pop up, after entering the formula's and have it ask which columns should be sorted first, second and third; or would it be simpler to simply ask what col for first sort, then have another popup ask for 2nd sort and then a 3rd.......... I have little to no experience here and can only follow directions... Here's a small example: A = Source ID - I would like to sort all the data by this column as soon as all the data is selected. B = Aging report - Formula will show the highest date for this column C = names - no need to group D = Date - no need to sort E = A special Code and may do the sort by it instead of A - Example: let's say I have 32 rows of data when I export this document. When I run the macro I would like it to determine how many rows I have, add the formula for whatever I need in row 33 and ask me to sort by (give me the option of putting in which column) and then ask if there is secondary sort. Once that's completed, it should sort again. A B C D E ASTFED 7 Name Date 38fjmv7 978DJDE 6 Name Date dkie8fj8 Etc..... And finally I have a need, with this same worksheet, to subtotal numbers by either col A or col E or another column to be determined later. Any ideas?? Any help would be greatly appreciated. Thanks so much Bob Reynolds |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Formula's / Programming | Excel Discussion (Misc queries) | |||
Can I show a formula's result on a worksheet tab? | Excel Worksheet Functions | |||
Accessign formula's in worksheet that appear to have been hidden | Excel Discussion (Misc queries) | |||
where can i find a list of excell formula's? | New Users to Excel | |||
where can i find a list of excell formula's? | New Users to Excel |