Consulting

Results 1 to 2 of 2

Thread: Add new Row (table)

  1. #1

    Add new Row (table)

    Hi everyone,

    I'm new to VBA and I'm looking for a way to insert a new row at the end of my table.

    The row needs to use all formats and formulas from the row above.

    I found a code which I tried to implement, but it is not working...

    (As far as I understand, I only have to make changes to the tablename and sheetname...., or are there other things to consider?)

    Can someone please tell me what is wrong with the code or what else I need to do in order to be able to use this code?

    I would appreciate your help!

    Thanks in advance
    __________________________________________________________

    Sub AddDataRow(Table1 As String, values() As Variant)
    Dim sheet As Worksheet
    Dim table As ListObject
    Dim col As Integer
    Dim lastRow As Range
    
    Set sheet = ActiveWorkbook.Worksheets("Sheet1")
    Set table = sheet.ListObjects.Item(Table1)
    'First check if the last row is empty; if not, add a row
    If table.ListRows.Count > 0 Then
    Set lastRow = table.ListRows(table.ListRows.Count).Range
    For col = 1 To lastRow.Columns.Count
    If Trim(CStr(lastRow.Cells(1, col).Value)) <> "" Then
    table.ListRows.Add
    Exit For
    End If
    Next col
    End If
    'Iterate through the last row and populate it with the entries from values()
    Set lastRow = table.ListRows(table.ListRows.Count).Range
    For col = 1 To lastRow.Columns.Count
    If col <= UBound(values) + 1 Then lastRow.Cells(1, col) = values(col - 1)
    Next col
    End Sub

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Being structured, tables handle a lot of that for you, so you can simplify the code

    Sub AddDataRow(SheetName As String, Table1 As String, values As Variant)
    Dim sheet As Worksheet
    Dim table As ListObject
    
        Set sheet = ActiveWorkbook.Worksheets(SheetName)
        Set table = sheet.ListObjects.Item(Table1)
        
        'First check if the last row is empty; if not, add a row
        If table.ListRows.Count > 0 Then
            
            table.ListRows.Add
            
            'Iterate through the last row and populate it with the entries from values()
             table.DataBodyRange.Rows(table.ListRows.Count) = values
        End If
    End Sub
    and you would use it like so

    Sub test()
        AddDataRow "Sheet1", "Table3", Array("Rusty", 8, DateSerial(1994, 7, 11))
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Tags for this Thread

Posting Permissions

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