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!
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.