Insert Row After Series

[FrankM]

I think this is possible but I'm not certain of the way the code would look.
I have an extract that comes to me in Excel format and I have a Macro that
runs through the Excel file reformatting it. The extract will have several
hundred rows.

Column I has a State description (I believe there are a couple dozen
descriptions) and what I need to do is add a blank row after each state.

Example ...

Pending
Pending
Pending
In Process
In Process
Active
Active
Active

would become

Pending
Pending
Pending

In Process
In Process

Active
Active
Active

I guess what I would like would be to add code to the existing Macro that
basically says everytime the value in column I changes add a row. Any ideas.

I hope this makes sense.

 

[StumpedAgain]

This goes through and looks at column A and inserts a row where there are
changes. Hope this helps!

Option Explicit
Sub insertrows()

Dim curselection As Range

Set curselection = Range("A1") 'or wherever you start

Do While curselection <> ""

If curselection <> curselection.Offset(1, 0) Then
curselection.Offset(1, 0).EntireRow.Insert
Set curselection = curselection.Offset(1, 0)
End If

Set curselection = curselection.Offset(1, 0)

Loop

End Sub

 

[FrankM]

That worked perfectly. Thank you so much. Is there a way to have the inserted
row have a background color ... say yellow maybe?

I tried modifying the line
curselection.Offset(1, 0).EntireRow.Insert
to include
Interior.ColorIndex = 41
so it was
curselection.Offset(1, 0).EntireRow.Insert.Interior.ColorIndex = 41
but I got an error, I'm not surprised. It was a long shot but I thought it
was worth trying. Any ideas would be great.

Thanks again.

 

[StumpedAgain]

You could do:

curselection.Offset(1, 0).EntireRow.Insert
curselection.Offset(1, 0).EntireRow.Select
Selection.Interior.ColorIndex = 41 '41 is blue yellow is 6
Set curselection = curselection.Offset(1, 0)

Hope this helps!

 

[Rick Rothstein \(MVP - VB\)]

Add this line...

curselection.Offset(1, 0).EntireRow.Interior.ColorIndex = 41

right after this line...

curselection.Offset(1, 0).EntireRow.Insert

although ColorIndex 41 is not yellow on my system (it's blue).

Rick

 

[FrankM]

Thank you. Both Rick and SA you both helped me out quite a bit. I really
appreciate it.

Worked like a charm.

Yes I know the ColorIndex in my example was Blue not Yellow, I was just
picking random colors just for examples. Thank you for pointing me in the
right direction. I am very grateful.