How to insert blank row after every nth row?

genetist

Board Regular
Joined
Mar 29, 2013
Messages
66
Hi to all,
I want to insert a blank row after every nth row for example after every 10th row. I s there any trick for this or we have to use macros? to insert rows automatically?

Thanks,
Regards,
Genetist
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
I don't think there's a way to do it without code, but this would do it....

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 = 10 Then
        Rows(rw).Insert Shift:=xlDown
        cnt = 1
    Else
        cnt = cnt + 1
    End If
    rw = rw + 1
Loop While rw <> lr
End Sub
If you want a different count, just change this line....

Code:
    If cnt = 10 Then
to the number of rows you want to insert a row.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,953
Office Version
2013
Platform
Windows
Also
Code:
Sub InsertRowEveryXrows()
Dim r As Long, lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
    For r = 10 To lr Step 10
        Rows(r).Insert Shift:=xlDown
    Next r
End Sub
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,045
TRY BELOW CODE ITS ALLOW TO INSERT ROW AS PER YOUR INPUT PROVIDE YOU

Code:
Sub Insert_Row()
Dim my As Integer, ur As Integer
On Error GoTo Getout
ur = InputBox("Enter How Many Row Want to Insert")
my = InputBox("Enter How Many Rows Want to Skip")
Application.ScreenUpdating = False
If my = 0 Or ur = 0 Then Exit Sub
On Error GoTo Getout
Range("A" & 2 + my).Select
Do While ActiveCell.Value <> ""
5 Range(ActiveCell, ActiveCell.Offset(ur - 1, 0)).EntireRow.Insert
ActiveCell.Offset(1 + my + ur - 1, 0).Select
Loop
Getout:
Application.ScreenUpdating = True
End Sub
 

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
That won't work as intended Michael. Tested on about 980 rows and with each insert the last row goes down by one, that's why I went with a do loop instead of a for loop. You can't recalculate the last row in the middle of a for loop.

Which reminds me.... I forgot the last row recalculation, so this one will work....

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 = 10 Then
        Rows(rw).Insert Shift:=xlDown
        cnt = 1
        lr = Range("A" & Rows.Count).End(xlUp).Row
    Else
        cnt = cnt + 1
    End If
    rw = rw + 1
Loop While rw <> lr
End Sub
I suppose you could use a for loop counting backwards, but then you'd have to do some calculating to make sure it does it on the correct rows to be every x number rows from the top. It's just easier with the do loop and using count variables, but I've often been proved that easier is not always better. :)
 

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
TRY BELOW CODE ITS ALLOW TO INSERT ROW AS PER YOUR INPUT PROVIDE YOU
Sorry Kev, that doesn't work. I entered 1 row every 10 rows in your code and the first row it inserted was row 12, not row 10. The next row was at row 23 then 34, gets off my more and more. Should be 10, 20, 30, 40
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,607
Hi rjwebgraphix

Try this:

Code:
Option Explicit
Sub Macro1()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au

    Dim lngMyRow As Long
    Dim lngLastRow As Long
    Dim rngMyRange As Range
    
    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For lngMyRow = 10 To lngLastRow Step 10    
        
        If rngMyRange Is Nothing Then
            Set rngMyRange = Cells(lngMyRow, "A")
        Else
            Set rngMyRange = Union(rngMyRange, Cells(lngMyRow, "A"))
        End If
    
    Next lngMyRow
    
    rngMyRange.EntireRow.Insert
    
    Application.ScreenUpdating = True
    
    MsgBox "Rows have now been inserted"
        
End Sub
Regards,

Robert
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,045
Sorry Kev, that doesn't work. I entered 1 row every 10 rows in your code and the first row it inserted was row 12, not row 10. The next row was at row 23 then 34, gets off my more and more. Should be 10, 20, 30, 40
sorry
rjwebgraphix
in my file daily i am using same code its working fine i dont know why its not working in yours ?
 

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
Hi rjwebgraphix
Try this:
Wasn't my post, just pointing out the flaws in the other codes. The do loop I wrote works. Yours has a similar effect as kev's, where every 10 rows should be inserted at row 10, 20, 30, 40. etc.... yours did 10, 21, 32, 43.

btw, I get a kick out of it whenever I see your posts. My Dad uses a variation of Trebor on dbforums and when I saw you post the first time I thought you were my dad. :) LOL
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,607
Wasn't my post, just pointing out the flaws in the other codes.
Ah yes, sorry about that.

btw, I get a kick out of it whenever I see your posts. My Dad uses a variation of Trebor on dbforums and when I saw you post the first time I thought you were my dad. LOL
He's name Robert then? Must be a cool guy :cool:

Genetist, try this:

Code:
Option Explicit
Sub Macro1()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au

    Dim lngMyRow As Long
    Dim lngLastRow As Long
    Dim rngMyRange As Range
    
    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For lngMyRow = 10 To lngLastRow Step 10
    
        If lngMyRow <> 10 Then lngMyRow = lngMyRow - 1
                
        If rngMyRange Is Nothing Then
            Set rngMyRange = Cells(lngMyRow, "A")
        Else
            Set rngMyRange = Union(rngMyRange, Cells(lngMyRow, "A"))
        End If
    
    Next lngMyRow
    
    rngMyRange.EntireRow.Insert
    
    Application.ScreenUpdating = True
    
    MsgBox "Rows have now been inserted"
        
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,134
Messages
5,484,932
Members
407,474
Latest member
Pam Sander

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top