Inserting Blank Rows

[Amanda C]

Hello!

I tried to search for this, but did not find what I was looking for. Sorry if this was already posted.

I believe there is a macro that would work for this.

I am trying to add 4 blank rows after each of my filled rows. I have 1,000 or filled rows that need the blanks added to them.

Something like:

Current:
Data
Data
Data
Data

Need:
Data
Blank
Blank
Blank
Blank
Data
Blank
Blank
Blank
Blank
Data
Blank
Blank
Blank
Blank
Data

And so on for all the rows in the sheet.

I just started to learn macro's and this one seems to be a bit over my head. I found some for adding just one row, but couldn't figure out how to change it to 4.

I appreciate any help!

Thank you!

 

[SirJB7]

Hi, Amanda C!

Give a try at this code:

Option Explicit

Sub NForThePriceOfOne()
    ' constants
    Const ksWS = "Hoja1"
    Const ksRange = "$A$2:$B$11"
    Const kiBlank = 4
    ' declarations
    Dim rng As Range
    Dim I As Long, J As Long, K As Long
    ' start
    Set rng = Worksheets(ksWS).Range(ksRange)
    K = rng.Row
    ' process
    With Worksheets(ksWS)
        For I = 2 To rng.Rows.Count
            J = K + 1
            .Rows(J & ":" & J + 3).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            K = K + kiBlank + 1
        Next I
    End With
    ' end
    Set rng = Nothing
    Beep
End Sub

Regards!

 

[Amanda C]

Thank you!

I keep getting the error:

Run-Time error '9':
Subscript out of range

I am not sure what that means. I think I am out depth on this one.

Any idea?

 

[SirJB7]

Hi, Amanda C!
I used a sample range A2:B11 in first worksheet (Hoja1 in my Excel version in Spanish). You should adjust the worksheet name if your program is in other language, and surely you'll have to adjust the range to your case.
Otherwise consider uploading a sample file.
Regards!

 

[Amanda C]

I should have known that!

Thank you so very much! It worked perfectly. You are an excel life saver!

 

[SirJB7]

Hi, Amanda C!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!

 

[SirJB7]

Hi, Amanda C!
Just in case you didn't notice (I'm sure you did!... )...
Changing the value of constant kiBlank you can insert a different number of blank lines. And no, no reverse or back button to remove them... unless someone asks for it.
Regards!