Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested Subtotal and Macro
Sounds like it is using selection and the selection isn't being expanded on
each iteration. after each subtotal command put in a line like selection.CurrentRegion.Select -- Regards, Tom Ogilvy "KJaquish" wrote in message ... I am having a problem creating a macro to create nested subtotals. I used the Record Macro feature and when I stop recording, the data looks just as I want it. However, when I run the macro, it displays one of the Grand Totals near, but not at the bottom of the data, and it doesn't include a subtotal for the last group of data at all. The Grand Total that does appear at the bottom appears correct, but the odd formatting and placement make it difficult to use. How can I get the macro to return the same results that I get if I create the subtotals manually? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested Subtotal and Macro
Thanks for the suggestion, but that didn't work either.
You are correct that the selection is not being expanded - it is stopping before it gets quite to the bottom. Using your suggested command selected the cells just before the Grand Total, which was an improvement, but still not getting it where it needs to be. I've tried naming a range that might expand to include the grand total, but that doesn't seem to work either. The Grand Total is put just outside the range. For now I have split the macro in two and am executing it in separate steps, but it would be much slicker if I could find a way to streamline it. Thanks! -----Original Message----- Sounds like it is using selection and the selection isn't being expanded on each iteration. after each subtotal command put in a line like selection.CurrentRegion.Select -- Regards, Tom Ogilvy "KJaquish" wrote in message news:BC838A0B-604A-4DF9-A7B1- ... I am having a problem creating a macro to create nested subtotals. I used the Record Macro feature and when I stop recording, the data looks just as I want it. However, when I run the macro, it displays one of the Grand Totals near, but not at the bottom of the data, and it doesn't include a subtotal for the last group of data at all. The Grand Total that does appear at the bottom appears correct, but the odd formatting and placement make it difficult to use. How can I get the macro to return the same results that I get if I create the subtotals manually? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested Subtotal and Macro
Then just select a single cell before each execution of the subtotal command
(a cell in the data area). Usually subtotal can find the right area. Range("A1").Select selection.subtotal this assumes you are using selection.Subtotal rather than specifying a range. However, unless there is a blank line between grand total and the data, then currentregion should work. -- Regards, Tom Ogilvy wrote in message ... Thanks for the suggestion, but that didn't work either. You are correct that the selection is not being expanded - it is stopping before it gets quite to the bottom. Using your suggested command selected the cells just before the Grand Total, which was an improvement, but still not getting it where it needs to be. I've tried naming a range that might expand to include the grand total, but that doesn't seem to work either. The Grand Total is put just outside the range. For now I have split the macro in two and am executing it in separate steps, but it would be much slicker if I could find a way to streamline it. Thanks! -----Original Message----- Sounds like it is using selection and the selection isn't being expanded on each iteration. after each subtotal command put in a line like selection.CurrentRegion.Select -- Regards, Tom Ogilvy "KJaquish" wrote in message news:BC838A0B-604A-4DF9-A7B1- ... I am having a problem creating a macro to create nested subtotals. I used the Record Macro feature and when I stop recording, the data looks just as I want it. However, when I run the macro, it displays one of the Grand Totals near, but not at the bottom of the data, and it doesn't include a subtotal for the last group of data at all. The Grand Total that does appear at the bottom appears correct, but the odd formatting and placement make it difficult to use. How can I get the macro to return the same results that I get if I create the subtotals manually? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested Subtotal and Macro
Oh - that's the problem - there is a blank line between
the data and the Grand Total. I'll get rid of that and try the CurrentRegion again. Thanks again for your help! Karen -----Original Message----- Then just select a single cell before each execution of the subtotal command (a cell in the data area). Usually subtotal can find the right area. Range("A1").Select selection.subtotal this assumes you are using selection.Subtotal rather than specifying a range. However, unless there is a blank line between grand total and the data, then currentregion should work. -- Regards, Tom Ogilvy wrote in message ... Thanks for the suggestion, but that didn't work either. You are correct that the selection is not being expanded - it is stopping before it gets quite to the bottom. Using your suggested command selected the cells just before the Grand Total, which was an improvement, but still not getting it where it needs to be. I've tried naming a range that might expand to include the grand total, but that doesn't seem to work either. The Grand Total is put just outside the range. For now I have split the macro in two and am executing it in separate steps, but it would be much slicker if I could find a way to streamline it. Thanks! -----Original Message----- Sounds like it is using selection and the selection isn't being expanded on each iteration. after each subtotal command put in a line like selection.CurrentRegion.Select -- Regards, Tom Ogilvy "KJaquish" wrote in message news:BC838A0B-604A-4DF9-A7B1- ... I am having a problem creating a macro to create nested subtotals. I used the Record Macro feature and when I stop recording, the data looks just as I want it. However, when I run the macro, it displays one of the Grand Totals near, but not at the bottom of the data, and it doesn't include a subtotal for the last group of data at all. The Grand Total that does appear at the bottom appears correct, but the odd formatting and placement make it difficult to use. How can I get the macro to return the same results that I get if I create the subtotals manually? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtotal with sumif nested | Excel Worksheet Functions | |||
nested subtotal with sumproduct | Excel Worksheet Functions | |||
follow up to nested subtotal/sumproduct | Excel Worksheet Functions | |||
Nested --- Subtotal IF function | Excel Worksheet Functions | |||
Nested Subtotal Formula | Excel Worksheet Functions |