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

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.

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

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.

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

PERFECT!! Thanks Faster!