Insert a new row every '2501' rows with content of '--------' via VBA

riteon

New Member
Joined
Apr 30, 2010
Messages
24
Hello, I have managed to find a VBA script that inserts a gap every 2501 rows perfectly, but rather than just a blank cell, I'd like it to insert some dashes --------

Here is my existing code;

VBA Code:
Sub InsertRowEveryXrows()

Dim rw As Long
Dim lr As Long
Dim cnt As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
rw = 1
cnt = 1
Do
    If cnt = 2501 Then
        Rows(rw).Insert Shift:=xlDown
        cnt = 1
    Else
        cnt = cnt + 1
    End If
    rw = rw + 1
Loop While rw <> lr
End Sub

I presume something goes under this line
VBA Code:
Rows(rw).Insert Shift:=xlDown
but I'm not sure how to accomplish this.

Would one of the kind souls here be able to help me out?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Rather than moving one row at a time, we can jump by 2501 each time, i.e.
VBA Code:
Sub InsertRowEveryXrows()

    Dim r As Long
    Dim lr As Long
    Dim x As Long
    
'   Enter value of rows to jump by
    x = 2501

    lr = Range("A" & Rows.Count).End(xlUp).Row
    If lr < x Then Exit Sub

    For r = x To lr Step x
        Rows(r).Insert Shift:=xlDown
        Cells(r, "A") = "--------"
    Next r
    
End Sub
 
Upvote 0
Solution
Rather than moving one row at a time, we can jump by 2501 each time, i.e.
VBA Code:
Sub InsertRowEveryXrows()

    Dim r As Long
    Dim lr As Long
    Dim x As Long
   
'   Enter value of rows to jump by
    x = 2501

    lr = Range("A" & Rows.Count).End(xlUp).Row
    If lr < x Then Exit Sub

    For r = x To lr Step x
        Rows(r).Insert Shift:=xlDown
        Cells(r, "A") = "--------"
    Next r
   
End Sub
Perfection! Many many thanks :)
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top