Archive of Mr Excel Message Board


Back to Data in Excel archive index
Back to archive home

Insert A Row After Each Criteria Change

Posted by Brian on August 13, 2001 1:25 PM
I have a sheet that has a list of months in column A and then other data in following columns. I would like to do the following using a macro:
1) Insert a new "total" row after each month
2) Then sum each column in this new row.

My data looks something like:
Month: Value
05 1
05 3
05 5
06 1
06 2
06 2
07 3
07 3
07 3

And with the macro do this:

Month: Value
05 1
05 3
05 5

Sum 9

06 1
06 2
06 2

Sum 5

07 3
07 3
07 3

Sum 9

I'm not exactly sure how to write the vb to add the new row and then do the sum at the end of each group.

Thanks,
Brian


Re: Insert A Row After Each Criteria Change

Posted by faster on August 13, 2001 1:37 PM

Use Subtotals:

Data/Subtotals - at each change in month, subtotal #

Make sure you have a complete table with no blank
rows, and column headings.


Re: Insert A Row After Each Criteria Change

Posted by Brian on August 14, 2001 7:01 AM
Thanks, I tried that and it worked perfectly. But is there a way to highlight the subtotal rows. I tried conditional formatting but no luck.

Thanks,
Brian


Re: Insert A Row After Each Criteria Change

Posted by faster on August 14, 2001 9:10 AM

Yes. You will need to add a button to your tool bar.
Right click your tool bar and select customize.
From the Commands Tab select Edit. The button you
need is called Select Visible Cells. Drag it and drop
it on your existing toolbar.

Using the Outline feature of Subtotals (it should be
in the top left of your spreadsheet)click 2, this
should show you only the subtotal rows. Highlight
the rows with your mouse and click the Select Visible
Cells button. Color the rows and click 3 to expand
the subtotal data.

I hope this helps.


PERFECT!! Thanks Faster!

Posted by Brian on August 14, 2001 9:37 AM


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.