下面将创建一个表格来拟合数据，假设没有额外的数据单元格：

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table1"

如果需要强制列只包含"A:AG"，请用途：

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion.Columns("A:AG"), , xlYes).Name = "Table1"

ActiveSheet.ListObjects.Add(...).Name = "Table1"是一个录制的宏创建表的方式。为了避免命名冲突，我将避免使用通用的Table1作为名称。
如果名称不重要，请使用：

ActiveSheet.ListObjects.Add xlSrcRange, Range("A1").CurrentRegion.Columns("A:AG"), , xlYes

如果工作表上只有一个表，则可以将其引用为：

ActiveSheet.ListObjects(1)

删除底部空行（应用于Excel表格（ListObject））

Sub DeleteEmptyBottomRowsTEST()
     
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim tbl As ListObject: Set tbl = ws.ListObjects("Table1")
    
    ' Remove possible filters.
    ' The Find method will fail if the table is filtered.
    If tbl.ShowAutoFilter Then
        If tbl.AutoFilter.FilterMode Then tbl.AutoFilter.ShowAllData
    End If
     
    Dim rg As Range: Set rg = tbl.DataBodyRange
    If rg Is Nothing Then Exit Sub
     
    DeleteEmptyBottomRows rg

End Sub

Sub DeleteEmptyBottomRows(ByVal rg As Range)
    
    Dim lCell As Range
    Set lCell = rg.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lCell Is Nothing Then rg.Delete xlShiftUp: Exit Sub
    
    Dim rOffset As Long: rOffset = lCell.Row - rg.Row + 1
    Dim rCount As Long: rCount = rg.Rows.Count
    If rOffset = rCount Then Exit Sub
    
    Dim rResize As Long: rResize = rCount - rOffset
    rg.Resize(rResize).Offset(rOffset).Delete xlShiftUp

End Sub