Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Lines | Excel Worksheet Functions | |||
Insert lines in more than one worksheet at a time... | Excel Worksheet Functions | |||
insert lines | Excel Discussion (Misc queries) | |||
Insert lines when x< y | Excel Programming | |||
how to automatically insert blank lines in between non-blank lines | Excel Programming |