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!
Minor variation on Geoff's answer.
New Data in Array:
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()
Set lastRow = table.ListRows(table.ListRows.Count).Range
For col = 1 To lastRow.Columns.Count
If col <= UBound(NewData) + 1 Then lastRow.Cells(1, col) = NewData(col - 1)
Next col
End Sub
New Data in Horizontal Range:
Sub AddDataRow(tableName As String, NewData As Range)
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 table 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
'Copy NewData to new table record
Set lastRow = table.ListRows(table.ListRows.Count).Range
lastRow.Value = NewData.Value
End Sub
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
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
This should help you.
Dim Ws As Worksheet
Set Ws = Sheets("Sheet-Name")
Dim tbl As ListObject
Set tbl = Ws.ListObjects("Table-Name")
Dim newrow As ListRow
Set newrow = tbl.ListRows.Add
With newrow
.Range(1, Ws.Range("Table-Name[Table-Column-Name]").Column) = "Your Data"
End With
Source: Stackoverflow.com