VBA：计算表中的行数（列表对象）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29639474/
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
VBA: Counting rows in a table (list object)
提问by user2412489
I am trying to write some VBA in Excel that can take the name of a table (list object) as a parameter and return the number of rows.
我正在尝试在 Excel 中编写一些 VBA，它可以将表（列表对象）的名称作为参数并返回行数。
The following works, but isn't allowing me to pass in a string with the table name.
以下有效，但不允许我传入带有表名的字符串。
MsgBox ([MyTable].Rows.Count)
The following gives the error:
以下给出了错误：
Object required
所需对象
v_MyTable = "MyTable"
MsgBox (v_MyTable.Rows.Count)
The following gives the error:
以下给出了错误：
Object variable or With block variable not set
对象变量或未设置块变量
v_MyTable_b = "[" & "MyTable" & "]"
MsgBox(v_MyTable_b.Rows.Count)
I also tried working with ListObjects, which I am new to. I get the error:
我还尝试使用我不熟悉的 ListObjects。我收到错误：
Object doesn't support this property or method
对象不支持此属性或方法
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("MyTable")
MsgBox(tbl.Rows.Count)
Thanks for any help!
谢谢你的帮助！
回答by
You need to go one level deeper in what you are retrieving.
您需要更深入地了解要检索的内容。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("MyTable")
MsgBox tbl.Range.Rows.Count
MsgBox tbl.HeaderRowRange.Rows.Count
MsgBox tbl.DataBodyRange.Rows.Count
Set tbl = Nothing
More information at:
更多信息请访问：
ListObject Interface
ListObject.Range Property
ListObject.DataBodyRange Property
ListObject.HeaderRowRange Property
ListObject 接口
ListObject.Range 属性
ListObject.DataBodyRange 属性
ListObject.HeaderRowRange 属性
回答by Jalal
You can use this:
你可以使用这个：
Range("MyTable[#Data]").Rows.Count
You have to distinguish between a table which has either one row of data or no data, as the previous code will return "1" for both cases. Use this to test for an empty table:
您必须区分具有一行数据或没有数据的表，因为在这两种情况下，前面的代码都将返回“1”。使用它来测试一个空表：
If WorksheetFunction.CountA(Range("MyTable[#Data]"))
回答by Sangamesh B M
You can use:
您可以使用：
Sub returnname(ByVal TableName As String)
MsgBox (Range("Table15").Rows.count)
End Sub
and call the function as below
并调用函数如下
Sub called()
returnname "Table15"
End Sub
