Add new Row (table)

[filipeandrad]

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

[Bob Phillips]

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