how to insert multiple rows in excel 2010 between data

**Drum** · 05-17-2012, 07:19 PM

I am trying to insert rows in between part numbers or job numbers on the attached spreadsheet. I am new to this forum and it seems really helpful. I have the following code, but unsuccessful. It would be awesome if someone could improve upon this code.

Sub InsertRowsV4()

Dim lLastRow As Long
Dim i As Long
Application.ScreenUpdating = False
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = lLastRow - 1 To 2 Step -1
If Range("A" & i + 1).Value - Range("A" & i).Value > 0 Then
On Error Resume Next
Range("A" & i + 1).Resize(Range("A" & i + 1).Value - Range("A" & i).Value - 1, 1).EntireRow.Insert
On Error GoTo 0
End If
Next 'i
Application.ScreenUpdating = True
End Sub

**Trebor76** · 05-17-2012, 09:39 PM

Hi Drum,

Welcome to the forum!!

Try this where a row is inserted at a change in Col A (so ensure your data is sorted by that column):

Option Explicit
Sub InsertRowsV4()

    'http://www.excelforum.com/excel-general/832021-how-to-insert-multiple-rows-in-excel-2010-between-data.html

    Dim lLastRow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    lLastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = lLastRow To 2 Step -1
        If i > 2 And Range("A" & i).Value <> Range("A" & i - 1).Value Then
            Rows(i).EntireRow.Insert
        End If
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

HTH

Robert

PS You need to wrap your code with tags as I have done