使用 VBA 动态调整列表对象表的大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40323014/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Resize Listobject Table dynamically with VBA
提问by Ryan Chatoeala
I want to change the size of the object tables through VBA, I tried modifying the code from MSDN about the listobject.resizemethod, but I want to dynamically if a data loaded in each row.
我想通过 VBA 更改对象表的大小，我尝试修改 MSDN 中有关该listobject.resize方法的代码，但我想动态地在每行中加载数据。
The code I'm trying but to no avail:
我正在尝试但无济于事的代码：
Sub resizedata()
Dim ws As Worksheet
Dim ob As ListObject
Dim Lrow1 As Long
Lrow1 = Sheets("db_goods").Cells(Rows.Count, "E").End(xlUp).Row
Set ws = ActiveWorkbook.Worksheets("db_goods")
Set ob = ws.ListObjects("Table1")
ob.Resize Range("A1" & Lrow1)
End Sub
回答by MJH
The problem is Range("A1" & Lrow1)returns a Range of $A$112, because you are passing the Rangefunction the result of the catenation of "A1" & "12".
问题是Range("A1" & Lrow1)返回范围为$A$112，因为您正在向Range函数传递的串联结果"A1" & "12"。
Try replacing this line:
尝试替换此行：
ob.Resize Range("A1" & Lrow1)
With this one:
有了这个：
ob.Resize ob.Range.Resize(Lrow1)
回答by JohnyL
There's way avoiding calculating last row:
有办法避免计算最后一行：
Sub ResizeListDyn()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(1)
tbl.Resize tbl.Range.CurrentRegion
End Sub
回答by pstraton
If you need to resize only the row-dimension:
如果您只需要调整行维的大小：
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("YourTableName")
With tbl.Range
tbl.Resize .Resize(.CurrentRegion.Rows.Count) 'NOTE: unlike the Range.Resize proprty, the Table.Resize
'method's argument is a Range object (not a size spec).
End With
Resizing only the column-dimension would be symmetrical:
仅调整列维的大小将是对称的：
With tbl.Range
tbl.Resize .Resize(, .CurrentRegion.Columns.Count)
End With
