Insertion of blank row in between every 5th row using a macro

Dear Experts:

I would like to run a macro that ...
... inserts a blank row in between every 5th row on the current worksheet.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Sub macro()

Idx = 5
Do Until Range("A" & Idx) = ""
    Range("A" & Idx).EntireRow.Insert
    Idx = Idx + 5
Loop

End Sub

Regards
0
Rob HensonFinance AnalystCommented:
Rgonzo1971 - slight issue which I see in your routine, assuming I am interpreting correctly.

First run will insert row at row 5, correctly. Second Run (Idx + 5 ) will insert row at row 10, based on original rows, should this have gone in at the now new row 11; third run same issue, new row at row 15 but this will now be 2 rows out.

Maybe using ActiveCell.Offset would be better:

Sub InsertRows()

Range("A6").Select
Do Until ActiveCell.Value = ""
Selection.EntireRow.Insert
ActiveCell.Offset(5,0).Select
Loop

End Sub

Thanks
Rob H
0
Rgonzo1971Commented:
@ Rob
Sure, I had a probably wrong interpretation of the question.
for groups of 5 lines

Sub macro()
Idx = 6
Do Until Range("A" & Idx) = ""
    Range("A" & Idx).EntireRow.Insert
    Idx = Idx + 6
Loop

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Martin LissOlder than dirtCommented:
It's easier to do it backward, and this macro avoids the screen flickering.
Sub InsertBlankRows()
Dim lngIndex As Long

Application.ScreenUpdating = False

With Sheets("Sheet1") ' change the name of the sheet if necessary
    For lngIndex = .UsedRange.Rows.Count - 4 To 2 Step -5
        .Cells(lngIndex, 1).EntireRow.Insert
    Next
End With

Application.ScreenUpdating = True
End Sub

Open in new window

0
krishnakrkcCommented:
Here is another method...

Option Explicit

Sub kTest()
    
    Dim r   As Long, rs As Long, c As Long
    
    Application.ScreenUpdating = False
    
    With Range("a1").CurrentRegion
        r = .Rows.Count
        c = .Columns.Count
    End With
    
    Columns(1).Insert
    With Range("a2:a" & r)
        .Formula = "=int((rows(a$2:a2)-1)/5)+1"
        .Value = .Value
    End With
    Range("a" & r + 1).Value = 1
    rs = Range("a" & r).Value
    Range("a" & r + 1).AutoFill Destination:=Range("a" & r + 1).Resize(rs), Type:=xlFillSeries
    
    Range("a2").Resize(rs + r - 1, c + 1).Sort Range("a2"), 1, Header:=2
    Columns(1).Delete
    Application.ScreenUpdating = True
    
End Sub

Open in new window


Kris
0
Andreas HermleTeam leaderAuthor Commented:
Dear all:

thank you very much for this superb and overwhelming support.  Sorry for not getting back to you earlier.

I will test your approaches and let you know tomorrow. Thank you again.

Regards, Andreas
0
Andreas HermleTeam leaderAuthor Commented:
Dear all,

Codes by Rob Henson and Rgonzo work just fine. Thank you very much for it.

The more 'sophisticated' codes by Martin and Krishna do not work entirely properly.

I have attached a sample file (with codes included) for your convenience to show the differences.

Regards, Andreas

every5throw-insert-blank-row.xlsm
0
krishnakrkcCommented:
I thought column header is there.  

Anyway try this version.

Sub kTest()
    'By Krishna
    
    Dim r   As Long, rs As Long, c As Long
    
    Application.ScreenUpdating = False
    
    With Range("a1").CurrentRegion
        r = .Rows.Count
        c = .Columns.Count
    End With
    
    Columns(1).Insert
    With Range("a1:a" & r)
        .Formula = "=int((rows(a$1:a1)-1)/4)+1"
        .Value = .Value
    End With
    Range("a" & r + 1).Value = 1
    rs = Range("a" & r).Value
    Range("a" & r + 1).AutoFill Destination:=Range("a" & r + 1).Resize(rs), Type:=xlFillSeries
    
    Range("a1").Resize(rs + r - 1, c + 1).Sort Range("a1"), 1, Header:=2
    Columns(1).Delete
    Application.ScreenUpdating = True
    
End Sub

Open in new window


Kris
0
Andreas HermleTeam leaderAuthor Commented:
Ok, krishna, this did the trick. Thank you very much for it.

Regards, Andreas
0
Andreas HermleTeam leaderAuthor Commented:
Thank you very much for your great and professional support. I really appreciate it.

Regards, Andreas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.