Insert Row After Series
 

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.

Insert Row After Series
 

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
--
-SA


"FrankM" wrote:

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.

Insert Row After Series
 

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" wrote:

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
--
-SA


"FrankM" wrote:

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.

Insert Row After Series
 

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!
--
-SA


"FrankM" wrote:

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" wrote:

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
--
-SA


"FrankM" wrote:

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.

Insert Row After Series
 

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" wrote in message
...
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" wrote:

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
--
-SA


"FrankM" wrote:

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.

Insert Row After Series
 

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.


"FrankM" wrote:

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" wrote:

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
--
-SA


"FrankM" wrote:

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.