Excel 07 - Macro to insert rows based on criteria

[smic]

Hi all,

Im hoping someone can assist me. Im looking to write a macro to insert rows into a set of data after each data group ends.

E.g. Coloum A contains the following data (1 per cell starting with A1, then A2 etc)

A1 = BB
A2 = BB
A3 = BB
A4 = CC
A5 = CC
A6 = CC
A7 = DD
A8 = DD
A9 = EE

I would like to run a macro which will insert rows after each similar data sets ends

The result would be the following

A1 = BB
A2 = BB
A3 = BB
A4 = (BLANK)
A5 = (BLANK)
A6 = CC
A7 = CC
A8 = CC
A9 = (BLANK)
A10 = (BLANK)
A11 =DD
A12 = DD
A13 = (BLANK)
A14 = (BLANK)
A15 = EE
A16 = EE

In the above example I have inserted 2 new lines between the data sets however having an easy method to change this would be great.

Hopefully my explanation was clear enough to show what I am trying to do.

Thanks in advance for your assistance

Mike

[smic]

Just a quick note that the data within the coloumn will change size, soIi need it to search for the last set of data (In the example it would be BB in A3) and insert the lines. However the next day the last occurance of BB might be in A7 and i would need the lines inserted there.

Thanks again!

[smic]

Bump, can anyone assist with the above query?

Cheers

Mike

[Leith Ross]

Hello smic,

This should work for your needs.

Please Login or Register  to view this content.

[smic]

Thankyou so much! It worked perfectly!!

[TBJV]

Hi folks,

I am a noob when in comes to macros but I, like smic, would be grateful for a solution that piggie backs on the macro written above. I tried this macro but I believe this only adds a cell in the column noted, in this case "B".

What would be needed in the script above to insert the ENTIRE empty row based on the same criteria as noted by smic.

Example: Before

A B C
1 10 AA X
2 5 AA X
3 7 AA X
4 3 BB X
5 8 BB X
6 6 BB X
7 1 CC X
8 3 CC X

After

A B C
1 10 AA X
2 5 AA X
3 7 AA X
4 (Empty)
5 3 BB X
6 8 BB X
7 6 BB X
8 (Empty)
9 1 CC X
10 3 CC X

Thanks in advance.

[arlu1201]

TBJV,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.