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

User · Answer

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

User · Answer

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  gt  0 Then         Set lastRow   table ListRows table ListRows Count  Range         If Application CountBlank lastRow   lt  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  lt   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  gt  0 Then         Set lastRow   table ListRows table ListRows Count  Range         If Application CountBlank lastRow   lt  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

User · Answer

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  gt  0 Then         Set lastRow   table ListRows table ListRows Count  Range         If Application CountBlank lastRow   lt  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  lt   UBound NewData    1 Then lastRow Cells 1  col    NewData col - 1      Next col End Sub

User · Answer

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

User · Answer

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  gt  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    lt  gt     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  lt   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

[vba] Function or sub to add new row and data to table

Examples related to vba

Examples related to excel