Function or sub to add new row and data to table

Question

I want to create a Sub that basically allows me to target an Excel table with a specific name and then insert a new row at the bottom and add data to that row at the same time. Then exit the sub. And if the table only has one row with no data in it, add the data to that row and then exit the sub.

How can I do this?

I was thinking something like this, in pseudo code:

Public Sub addDataToTable(ByVal strTableName as string, ByVal strData as string, ByVal col as integer)

ActiveSheet.Table(strTableName).Select
If strTableName.Rows.Count = 1 Then
    strTableName(row, col).Value = strData
Else
    strTable(lastRow, col).Value = strData
End if

End Sub

This is probably not valid as code at all, but it should explain what I'm after at least!

Answer 1

I needed this same solution, but if you use the native ListObject.Add() method then you avoid the risk of clashing with any data immediately below the table. The below routine checks the last row of the table, and adds the data in there if it's blank; otherwise it adds a new row to the end of the table:

Sub AddDataRow(tableName 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(tableName)

    '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
    Else
        table.ListRows.Add
    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

To call the function, pass the name of the table and an array of values, one value per column. You can get / set the name of the table from the Design tab of the ribbon, in Excel 2013 at least:

Example code for a table with three columns:

Dim x(2)
x(0) = 1
x(1) = "apple"
x(2) = 2
AddDataRow "Table1", x

Answer 2

Is this what you are looking for?

Option Explicit

Public Sub addDataToTable(ByVal strTableName As String, ByVal strData As String, ByVal col As Integer)
    Dim lLastRow As Long
    Dim iHeader As Integer

    With ActiveSheet.ListObjects(strTableName)
        'find the last row of the list
        lLastRow = ActiveSheet.ListObjects(strTableName).ListRows.Count
        'shift from an extra row if list has header
        If .Sort.Header = xlYes Then
            iHeader = 1
        Else
            iHeader = 0
        End If
    End With
    'add the data a row after the end of the list
    ActiveSheet.Cells(lLastRow + 1 + iHeader, col).Value = strData
End Sub

It handles both cases whether you have header or not.

Answer 3

Minor variation of phillfri's answer which was already a variation of Geoff's answer: I added the ability to handle completely empty tables that contain no data for the Array Code.

Sub AddDataRow(tableName As String, NewData As Variant)
    Dim sheet As Worksheet
    Dim table As ListObject
    Dim col As Integer
    Dim lastRow As Range

    Set sheet = Range(tableName).Parent
    Set table = sheet.ListObjects.Item(tableName)

    '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
        If Application.CountBlank(lastRow) < lastRow.Columns.Count Then
            table.ListRows.Add
        End If
    End If

    'Iterate through the last row and populate it with the entries from values()
    If table.ListRows.Count = 0 Then 'If table is totally empty, set lastRow as first entry
        table.ListRows.Add Position:=1
        Set lastRow = table.ListRows(1).Range
    Else
        Set lastRow = table.ListRows(table.ListRows.Count).Range
    End If
    For col = 1 To lastRow.Columns.Count
        If col <= UBound(NewData) + 1 Then lastRow.Cells(1, col) = NewData(col - 1)
    Next col
End Sub