Inserting multiple blank rows between already existing rows

Question

Hi All,

I have exported a query into excel. There are 300 rows and between each one of these rows, I need 5 blank rows to add different data to. Example:

John Doe

Jane Doe

Jimmy Doe

etc...

How do I insert a set number of blank rows between each name? On a global scale. Right now, I'm just highlighting the 5 rows I need and hitting insert, as you can imagine, this is taking me A LONG time. What I need is

John Doe

Jane Doe

Jimmy Doe

Answer 1

You could run this macro:

Sub InsertRows()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = m To 2 Step -1
        Range("A" & r).Resize(5).EntireRow.Insert
    Next r
    Application.ScreenUpdating = True
End Sub

Answer 2

If you want to insert 5 blank rows below the first 100 entries, 3 below the next 50, and none further down, you can use

Sub InsertRows()
    Dim r As Long
    Dim n As Long
    Application.ScreenUpdating = False
    For r = 150 To 2 Step -1
        Select Case r
            Case Is <= 100
                n = 5
            Case Else
                n = 3
        End Select
        Range("A" & r).Resize(n).EntireRow.Insert
    Next r
    Application.ScreenUpdating = True
End Sub

Answer 3

You could run this macro:

Sub InsertRows()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = m To 2 Step -1
        Range("A" & r).Resize(5).EntireRow.Insert
    Next r
    Application.ScreenUpdating = True
End Sub

This worked beautifully, thank you.

Side question - is it possible, out of 900 constituents, to add 5 blank lines to the first 100, then 3 blank lines for the next 50? This might be getting too complicated. I can always run 2 reports then combine.

Anyway, this was very helpful, THANK YOU!