Insert A Blank Row Where A New Category Begins

[geekette]

I am trying to automate a spreadsheet. I've written code in VB to run an autofilter and copy and paste selected data into a new worksheet. I now need to insert subtotals in the new sheet where a name in a column changes.

So for example I have 5 rows of data where the source name = A. On the 6th row the source name = B. Inbetween these two rows I need to insert a subtotal to sum the data for source name A, and then continue down for source B, C, D etc...

Is this possible to do?

I will be so greatful for any help on this

 

[erik.van.geit]

Hi, geekette
Welcome to the Board !!!!!

Excel has a SUBTOTAL function
see menu DATA

kind regards,
Erik

 

[geekette]

Thank you so much - it is always something so simple!!!

 

[geekette]

Hi, I have another question about subtotaling. I used the method above and have included it within a macro which is great. Next I want to colour each subtotaled section in a different colour. Currently I do this manually.

Ideally I need to the first subtotalled section dark blue, the next light blue and alternate this until there are no more subtotals. Is there anyway I can do this?

 

[Pekkavee]

You can use one color like this with conditional formatting.( Edit: Sorry this works only for "total" rows, and not the whole section. I didn't read you question right. )

If you have "Total" texts in column A put in A1 cell conditional formatting
with Formula is option

=ISNUMBER(FIND("Total",$A1,1))

and then select the color.

Then OK, OK.

Then copy your format to all cells

Pekka