Insert two lines each time a value in Column L changes
 

I am trying to get Excel to insert two lines each time a value in Column L
changes. My code is below:

Dim rngB As Range
Set rngB = Range("L2")
While rngB.Value < ""
If rngB.Value < rngB.Offset(1).Value Then
rngB.Offset(1).Resize(2).EntireRow.Insert
Set rngB = rngB.Offset(1)
End If
Set rngB = rngB.Offset(1)
Wend

It works the first time, but then fails afterwards. Is this one of those
instances where you have to start from the bottom of the list and work up? I
though working from the bottom up was only necessary for deleting rows based
on some criteria, such as deleting rows with blanks.

Any suggestions?

Thanks a lot!!
Ryan--

--
RyGuy

Insert two lines each time a value in Column L changes
 

Start from the bottom up such as

Sub insertrowsifvaluechanges()
For i = Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
If Cells(i, "L") < Cells(i - 1, "L") Then Rows(i).Resize(2).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ryguy7272" wrote in message
...
I am trying to get Excel to insert two lines each time a value in Column L
changes. My code is below:

Dim rngB As Range
Set rngB = Range("L2")
While rngB.Value < ""
If rngB.Value < rngB.Offset(1).Value Then
rngB.Offset(1).Resize(2).EntireRow.Insert
Set rngB = rngB.Offset(1)
End If
Set rngB = rngB.Offset(1)
Wend

It works the first time, but then fails afterwards. Is this one of those
instances where you have to start from the bottom of the list and work up?
I
though working from the bottom up was only necessary for deleting rows
based
on some criteria, such as deleting rows with blanks.

Any suggestions?

Thanks a lot!!
Ryan--

--
RyGuy

Insert two lines each time a value in Column L changes
 

Awesome! That's why you're an MVP.
Ryan--

--
RyGuy


"Don Guillett" wrote:

Start from the bottom up such as

Sub insertrowsifvaluechanges()
For i = Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
If Cells(i, "L") < Cells(i - 1, "L") Then Rows(i).Resize(2).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ryguy7272" wrote in message
...
I am trying to get Excel to insert two lines each time a value in Column L
changes. My code is below:

Dim rngB As Range
Set rngB = Range("L2")
While rngB.Value < ""
If rngB.Value < rngB.Offset(1).Value Then
rngB.Offset(1).Resize(2).EntireRow.Insert
Set rngB = rngB.Offset(1)
End If
Set rngB = rngB.Offset(1)
Wend

It works the first time, but then fails afterwards. Is this one of those
instances where you have to start from the bottom of the list and work up?
I
though working from the bottom up was only necessary for deleting rows
based
on some criteria, such as deleting rows with blanks.

Any suggestions?

Thanks a lot!!
Ryan--

--
RyGuy

Insert two lines each time a value in Column L changes
 

Don showed you how to do it by looping backwards, but you can do it going
forward just by adjusting your macro to properly positioning rngB:

Sub AAA()
Dim rngB As Range
Set rngB = Range("L2")
While rngB.Value < ""
If rngB.Value < rngB.Offset(1).Value Then
rngB.Offset(1).Resize(2).EntireRow.Insert
Set rngB = rngB.Offset(2)
End If
Set rngB = rngB.Offset(1)
Wend

End Sub

--
Regards,
Tom Ogilvy


"ryguy7272" wrote:

I am trying to get Excel to insert two lines each time a value in Column L
changes. My code is below:

Dim rngB As Range
Set rngB = Range("L2")
While rngB.Value < ""
If rngB.Value < rngB.Offset(1).Value Then
rngB.Offset(1).Resize(2).EntireRow.Insert
Set rngB = rngB.Offset(1)
End If
Set rngB = rngB.Offset(1)
Wend

It works the first time, but then fails afterwards. Is this one of those
instances where you have to start from the bottom of the list and work up? I
though working from the bottom up was only necessary for deleting rows based
on some criteria, such as deleting rows with blanks.

Any suggestions?

Thanks a lot!!
Ryan--

--
RyGuy

Insert two lines each time a value in Column L changes
 

Aw shucks, Glad to help.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ryguy7272" wrote in message
...
Awesome! That's why you're an MVP.
Ryan--

--
RyGuy


"Don Guillett" wrote:

Start from the bottom up such as

Sub insertrowsifvaluechanges()
For i = Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
If Cells(i, "L") < Cells(i - 1, "L") Then Rows(i).Resize(2).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ryguy7272" wrote in message
...
I am trying to get Excel to insert two lines each time a value in Column
L
changes. My code is below:

Dim rngB As Range
Set rngB = Range("L2")
While rngB.Value < ""
If rngB.Value < rngB.Offset(1).Value Then
rngB.Offset(1).Resize(2).EntireRow.Insert
Set rngB = rngB.Offset(1)
End If
Set rngB = rngB.Offset(1)
Wend

It works the first time, but then fails afterwards. Is this one of
those
instances where you have to start from the bottom of the list and work
up?
I
though working from the bottom up was only necessary for deleting rows
based
on some criteria, such as deleting rows with blanks.

Any suggestions?

Thanks a lot!!
Ryan--

--
RyGuy