insert row (formatting ?)

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: insert row (formatting ?)

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is there an easy way to insert rows into a spreadsheet at set intervals? Ie , i need to add a row after every 11th line on an 800 line spreadsheet. The convential way of clicking on the row and then inserting a row is a bit much because of the size of the sheet.
    Thanks!
    DAwn F

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,657
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default

    You code run a simple macro, like below:

    Code:
    Sub MyInsertRows()
    
        Range("A1").Select
        For i = 1 To 73
            ActiveCell.Offset(11, 0).Select
            Selection.EntireRow.Insert
            ActiveCell.Offset(1, 0).Select
        Next i
        
    End Sub
    I am looping through 73 times because 800 / 11 =72.7.

    Hope this helps.

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    THANK YOU!!!!

  4. #4
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Too slow I know, but as I've written it I might as well post it .
    Sub RowInsertTest()
    Dim rw As Long, i As Integer

    rw = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    'the last used row in column A
    i = 11
    'the number of rows between inserts

    Application.ScreenUpdating = False
    Do Until rw <= 1
    If rw Mod i = 0 Then
    Rows(rw).Offset(1, 0).Insert
    rw = rw - 1
    Else: rw = rw - 1
    End If
    Loop
    Application.ScreenUpdating = True

    End Sub


    Richie

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
  •  

 

 
DMCA.com