VBA to insert rows between groups, add group header, and delete repeating values below header.

[KP_SoCal]

A while back, users Peter_SSs and Jazzed provided me some excellent feedback on how to insert a blank row after repeating cell values in a column. Click here to see the original thread.

Now I have a similar question, but it’s a little more complex. Rather than explain here, I’ll graph what I need below. Basically the Widgets are illustrated in column A, and the alpha characters (e.g. "adb") are all listed in column B.

Do any of you VBA experts have any suggestions on how I can get from my raw data to step 3 showing the expected results below?

raw data...
-----A Column----B Column
1
2---Widget1-----adb
3---Widget1-----klp
4---Widget1-----qpf
5---Widget2-----mes
6---Widget2-----xrz
7---Widget2-----uyr
8---Widget3-----dst
9---Widget3-----umn
10--Widget3-----kqh
11--Widget3-----vys

Step 1...(code starts by inserting two blank rows between groups)
-----A Column----B Column
1
2---Widget1-----adb
3---Widget1-----klp
4---Widget1-----qpf
5
6
7---Widget2-----mes
8---Widget2-----xrz
9---Widget2-----uyr
10
11
12--Widget3-----dst
13--Widget3-----umn
14--Widget3-----kqh
15--Widget3-----vys


Step 2...(code copies the first field in each group and pastes it directly above in the blank line and formats it to be bold face.)
-----A Column----B Column
1---Widget1
2---Widget1-----adb
3---Widget1-----klp
4---Widget1-----qpf
5
6---Widget2
7---Widget2-----mes
8---Widget2-----xrz
9---Widget2-----uyr
10
11---Widget3
12--Widget3-----dst
13--Widget3-----umn
14--Widget3-----kqh
15--Widget3-----vys


Step 3...(code deletes the repeating cells just below the header it just created back in step 2.)
-----A Column----B Column
1---Widget1
2-----------------adb
3-----------------klp
4-----------------qpf
5
6---Widget2
7-----------------mes
8-----------------xrz
9-----------------uyr
10
11---Widget3
12---------------dst
13---------------umn
14---------------kqh
15---------------vys


So step 3 is how the raw data would look after the code has been executed. Hope this makes sense. Thanks again for any feedback!

 

[Peter_SSs]

The only small issue that I had with Sub Rearrange() is that is copies into row 10 the formatting and row height from row 9. But not the end of the world. I plugged this piece of code at the end of your code and it did the trick.

Rows("10:10").Clear
Rows("10:10").RowHeight = 15

Anyways, beautiful work!!! As always, thanks Peter for your help!

KP

That's fine, but if the formatting of the row below is ok, you could try just amend the existing line by adding the blue bit

.EntireRow.Insert CopyOrigin:=xlFormatFromRightOrBelow

 

[KP_SoCal]

That's fine, but if the formatting of the row below is ok, you could try just amend the existing line by adding the blue bit

.EntireRow.Insert CopyOrigin:=xlFormatFromRightOrBelow

Great! Even better!!! Thanks Peter, I think this suggestion is much cleaner than mine. I'm going to go with it.

KP

 

[KP_SoCal]

Hi Peter, if you are available I have another scenario that pertains to the logic in this code that I would greatly appreciate your expertise. Below is the link to the question. I really appreciate any advice you can offer.

http://www.mrexcel.com/forum/showthread.php?t=483675