Inserting a blank row in between rows

**KimG** · 08-29-2008, 03:13 PM

Is there a formula to automatically do the below? I have a spreadsheet with 13,000 records on it and need this done ASAP? Any help would be appreciated. Thanks. Kim G

Here's how it looks:

12345
12345
12345
12345
12456
12456
12456
12457
12457

Here's how I want it to look:

12345
12345
12345
12345

12456
12456
12456

12457
12457

**KimG** · 08-29-2008, 03:14 PM

Sorry, I meant insert rows in between rows.

**VBA Noob** · 08-29-2008, 03:23 PM

Can't do it with a formula. So try some code.

Press Alt + F11 > insert new module > paste in the below > close VB editor > Press Alt + F8 > Select InsertRow.

Note: Assumes data In col A. Change the A reference to your column ref

Sub InsertRow()
Dim LastRow As Long, i As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False

    For i = LastRow To 2 Step -1
        If Cells(i, "A").Value <> Cells(i - 1, "A").Value Then
            Rows(i).Insert Shift:=xlDown
        End If
    Next i
    
Application.ScreenUpdating = True
End Sub

VBA Noob

**shg** · 08-29-2008, 03:28 PM

1. You could add a formula in an unused column (say, B). Then in B2, enter and copy down,

=IF(A2<>A1, 1, "")

2. Select that column, and do Edit > Go to > Special, Formula, and check Numbers

3. Insert > Rows

4. Delete or clear the temporary column