Consulting

Results 1 to 12 of 12

Thread: Adding additional rows to table

  1. #1
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location

    Adding additional rows to table

    The following code adds an additional row after each odd row
    Sub Tbl_Add_Rows()
        'In the selected tbl, insert a row after each odd row.
        Dim LastRow As Integer
        Application.ScreenUpdating = False
        LastRow = selection.Tables(1).rows.count
        If LastRow Mod 2 = 0 Then
            LastRow = LastRow
        Else: LastRow = LastRow - 1
        End If
        For i = LastRow To 2 Step -2
            selection.Tables(1).rows(i).Select
            With selection.Tables(1).rows(i)
                selection.InsertRowsAbove 1
            End With
        Next i
        Application.ScreenUpdating = True 
    End Sub
    I'm assuming then that if one knows the "pattern" of when you would like a row inserted you need to adapt either Mod(?) or For i = LastRow to 2 step(?) or both to adjust the insertion point. Is this correct?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Anyone care to comment?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    Administrator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,254
    Location
    The MOD part looks if the last row of the table is odd, if it is it subtracts 1 from the last row variable to make it even. So I suppose if you want it to start from an odd row you could omit that part.

    With Step set to -2.
    MOD part removed:
    1
    2
    3
    4
    5
    6
    7

    MOD part kept:
    1
    2
    3
    4
    5
    6
    7


    As for the loop part, you could adjust the step to make it insert rows every 3 or 4 etc. If you wanted to insert a row every other row (Step -1) then you would need to omit the MOD part unless your table ended on an even row, otherwise it will not add a row until the first even row.

    For example with Step set to -1 and a table with 7 rows.
    With MOD:
    1
    2
    3
    4
    5
    6
    7

    Without MOD:
    1
    2
    3
    4
    5
    6
    7
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2408, Build 17928.20080

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Interesting. As i was trying to understand the initial concept was to insert a new row every third row, and that there must have been a header row.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    Ted,

    I think I would use a Do ... Loop Until with an error handler. Also I wouldn't use the selection object:

    Sub Tbl_Add_RowsII()
    Dim oTbl As Table
    Dim oRow As Row
    Dim lngSeq As Long
      Application.ScreenUpdating = False
      Set oTbl = Selection.Tables(1)
      lngSeq = 2 'Use 2 to insert row after each odd or even row. Use 3 for every third, 4 for every fourth, etc.)
      Set oRow = oTbl.Rows(lngSeq) 'For every odd row
      'Set oRow = oTbl.Rows(lngSeq + 1) 'For every even row, or other sequence (e.g., every third, fourth, fifth etc.)
      Do
        oTbl.Rows.Add oRow
        On Error GoTo Err_Index
        Set oRow = oTbl.Rows(oRow.Index + lngSeq)
      Loop Until oRow.Index = oTbl.Rows.Count
      oTbl.Rows.Add oRow
    lbl_Exit:
      Exit Sub
    Err_Index:
      If oTbl.Rows.Last.Index - oRow.Index = lngSeq - 1 Then
        oTbl.Rows.Add
      End If
      Resume lbl_Exit
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Now thats a different approach to the subject, and conversely for inserting extra columns it could be

    Sub Tbl_Add_ColumnsII()
        Dim oTbl As Table
        Dim oCol As Column
        Dim lngSeq As Long
        Application.ScreenUpdating = False
        Set oTbl = Selection.Tables(1)
        lngSeq = 2 'Use 2 to insert row after each odd or even Column. Use 3 for every third, 4 for every fourth, etc.)
        Set oCol = oTbl.Columns(lngSeq) 'For every odd Column
        'Set oCol = oTbl.Columns(lngSeq + 1) 'For every even Column, or other sequence (e.g., every third, fourth, fifth etc.)
        Do
             oTbl.Columns.Add oCol
             On Error GoTo Err_Index
             Set oCol = oTbl.Columns(oCol.Index + lngSeq)
        Loop Until oCol.Index = oTbl.Columns.Count
        oTbl.Columns.Add oCol
        lbl_Exit:
        Exit Sub
        Err_Index:
        If oTbl.Columns.Last.Index - oCol.Index = lngSeq - 1 Then
             oTbl.Columns.Add
        End If
        Resume lbl_Exit 
    End Sub
    
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    Ted,

    Yes. Then only thing I would do differently is:

    Sub Tbl_Add_ColumnsII()
    Dim oTbl As Table
    Dim oCol As Column
    Dim lngSeq As Long
      Application.ScreenUpdating = False
      Set oTbl = Selection.Tables(1)
      
      lngSeq = 2 'Use 2 to insert row after each odd or even Column. Use 3 for every third, 4 for every fourth, etc.)
      'Set oCol = oTbl.Columns(lngSeq) 'For every odd Column
      Set oCol = oTbl.Columns(lngSeq + 1) 'For every even Column, or other sequence (e.g., every third, fourth, fifth etc.)
      Do
        oTbl.Columns.Add oCol
        On Error GoTo Err_Index
        Set oCol = oTbl.Columns(oCol.Index + lngSeq)
      Loop Until oCol.Index = oTbl.Columns.Count
      oTbl.Columns.Add oCol
    lbl_Exit:
      oTbl.AutoFitBehavior wdAutoFitWindow  'Added GKM
      Exit Sub
    Err_Index:
      If oTbl.Columns.Last.Index - oCol.Index = lngSeq - 1 Then
        oTbl.Columns.Add
      End If
      Resume lbl_Exit
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  8. #8
    VBAX Regular
    Joined
    Jan 2022
    Posts
    24
    Location

    Smile

    Hi! Since I am the one (vivka) who suggested the original code at a different forum, I can offer an improved version of it:
    Sub Tbl_Add_Row()
    'In the selected tbl, insert a row after each n-th row.
    
    Dim LastRow As Integer, RemainderRows As Integer, CountBy As Integer
        Application.ScreenUpdating = False
        
        CountBy = InputBox("Enter the number to count rows by:", "ENTER A ROW AFTER EACH...")
        LastRow = selection.Tables(1).rows.count
        RemainderRows = LastRow \ CountBy
        
        If LastRow Mod CountBy = 0 Then
            LastRow = LastRow
        Else: LastRow = CountBy * RemainderRows
        End If
        For i = LastRow To 2 Step -CountBy
            selection.Tables(1).rows(i).Select
            With selection.Tables(1).rows(i)
                selection.InsertRowsBelow 1
            End With
        Next i
        Application.ScreenUpdating = True
    End Sub

  9. #9
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    Vivka,

    This code doesn't address the question posted here. Code to insert a row after each odd row. Also, why do you declared some variables e.g., LastRow, Countby etc., but not others e.g., i?
    Greg

    Visit my website: http://gregmaxey.com

  10. #10
    VBAX Regular
    Joined
    Jan 2022
    Posts
    24
    Location
    gmaxey, thank you for your remarks! I'm still learning vba and, naturally, making mistakes. But, hopefully, I'm slowly progressing with the tips and codes from experts at different forums (you and Aussiebear are among those experts).

  11. #11
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Quote Originally Posted by Vladimir View Post
    ... (you and Aussiebear are among those experts).
    . Tis only one there who's the "expert' Vladimir, and it definitely isn't me.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  12. #12
    VBAX Regular
    Joined
    Jan 2022
    Posts
    24
    Location

Posting Permissions

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