Solved I would like to insert 2 rows when the customer code changes

April 23, 2012 at 11:39:17
Specs: Windows XP
Hello All, I have a spread sheet that is sorted by a 2 letter customer code, with multiple rows having the same code. I would like to insert 2 blank rows when the customer code changes to a new code. Thank you for your consideration.

See More: I would like to insert 2 rows when the customer code changes

Report •


✔ Best Answer
April 24, 2012 at 13:26:05
If you didn't change the code to look at Column D, then the code is going insert rows at every change in data in Column A.

That's what I meant when I said "Assuming your data starts in A1, this should work."

I meant the "customer codes" when I said "data".

Since you didn't say where your "customer codes" were listed, the best I could do was offer generic code.

Try this for your specific situation:

Sub InsertTwo()
'Determine length of data in column D
 lastCode = Range("D" & Rows.Count).End(xlUp).Row
'Loop through range in Reverse order, inserting 2 Rows at each change
   For nxtCode = lastCode To 6 Step -1
     If Cells(nxtCode + 1, 4) <> Cells(nxtCode, 4) Then
      Rows(nxtCode + 1 & ":" & nxtCode + 2).Insert
     End If
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
April 23, 2012 at 21:45:15
Hi,

You can do this by pressing ALT+F4.

Happy to help,

SupportStaff


Report •

#2
April 24, 2012 at 07:53:03
Thank you for taking the time to answer my question, but pressing Alt F4 closes the worksheet and no rows are inserted between the different customer codes.

Report •

#3
April 24, 2012 at 09:30:31
Try:

Alt-i, then the letter r to insert a row
Alt-i, then the letter c to insert a column

Then use the F4 key to Repeat the last Command
and insert more rows.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 24, 2012 at 09:47:33
Assuming your data starts in A1, this should work.

Sub InsertTwo()
'Determine length of data in column A
 lastCode = Range("A" & Rows.Count).End(xlUp).Row
'Loop through range in Reverse order, inserting 2 Rows at each change
   For nxtCode = lastCode To 1 Step -1
     If Cells(nxtCode + 1, 1) <> Cells(nxtCode, 1) Then
      Rows(nxtCode + 1 & ":" & nxtCode + 2).Insert
     End If
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
April 24, 2012 at 09:57:44
Thank you for your reply. This does work if I wanted to insert the rows maually, which I already do. I am actually looking for a macro that compares the data in D6 to D7. If the data is the same customer code, then it moves on to compare D7 to D8, and so on. When the comparison yields a "not equal to" condition, then 2 rows are inserted between the two different customer codes. But thanks again for your input.

Report •

#6
April 24, 2012 at 12:30:36
Thanks DerbyDad03! This one almost worked. The data actually starts in D6, and this macro inserted 2 rows between every existing row, so something is amiss.

Report •

#7
April 24, 2012 at 13:26:05
✔ Best Answer
If you didn't change the code to look at Column D, then the code is going insert rows at every change in data in Column A.

That's what I meant when I said "Assuming your data starts in A1, this should work."

I meant the "customer codes" when I said "data".

Since you didn't say where your "customer codes" were listed, the best I could do was offer generic code.

Try this for your specific situation:

Sub InsertTwo()
'Determine length of data in column D
 lastCode = Range("D" & Rows.Count).End(xlUp).Row
'Loop through range in Reverse order, inserting 2 Rows at each change
   For nxtCode = lastCode To 6 Step -1
     If Cells(nxtCode + 1, 4) <> Cells(nxtCode, 4) Then
      Rows(nxtCode + 1 & ":" & nxtCode + 2).Insert
     End If
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
April 25, 2012 at 09:32:35
DerbyDad03,

Sorry for the lack of clarification. This time it worked flawlessly. Thanks so much for taking the time to help me out. This really shortens the time it takes to me to format my report.

DTD


Report •


Ask Question