Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How to insert blank row after every nth row?

  1. #1
    Board Regular
    Join Date
    Mar 2013
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to insert blank row after every nth row?

    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

  2. #2
    Board Regular
    Join Date
    May 2010
    Location
    Cleveland, OH
    Posts
    588
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to insert blank row after every nth row?

    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.
    Work: Excel 2010 | Home: Apache Open Office 4.1.2
    When posting code. Please enclose your code in Code tags.
    Example:
    [code]Paste code here[/code]

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    15,761
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to insert blank row after every nth row?

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,038
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to insert blank row after every nth row?

    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

  5. #5
    Board Regular
    Join Date
    May 2010
    Location
    Cleveland, OH
    Posts
    588
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to insert blank row after every nth row?

    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.
    Work: Excel 2010 | Home: Apache Open Office 4.1.2
    When posting code. Please enclose your code in Code tags.
    Example:
    [code]Paste code here[/code]

  6. #6
    Board Regular
    Join Date
    May 2010
    Location
    Cleveland, OH
    Posts
    588
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to insert blank row after every nth row?

    Quote Originally Posted by kevatarvind View Post
    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
    Work: Excel 2010 | Home: Apache Open Office 4.1.2
    When posting code. Please enclose your code in Code tags.
    Example:
    [code]Paste code here[/code]

  7. #7
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,046
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to insert blank row after every nth row?

    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

  8. #8
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,038
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to insert blank row after every nth row?

    Quote Originally Posted by rjwebgraphix View Post
    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 ?

  9. #9
    Board Regular
    Join Date
    May 2010
    Location
    Cleveland, OH
    Posts
    588
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to insert blank row after every nth row?

    Quote Originally Posted by Trebor76 View Post
    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
    Work: Excel 2010 | Home: Apache Open Office 4.1.2
    When posting code. Please enclose your code in Code tags.
    Example:
    [code]Paste code here[/code]

  10. #10
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,046
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to insert blank row after every nth row?

    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

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •