VBA:计算表(列表对象)中的行 [英] VBA: Counting rows in a table (list object)

问题描述

我正在尝试在Excel中编写一些VBA，可以将表名(列表对象)作为参数并返回行数.

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.

以下方法有效，但不允许我传入带有表名的字符串.

The following works, but isn't allowing me to pass in a string with the table name.

MsgBox ([MyTable].Rows.Count)

以下给出了错误:

需要对象

v_MyTable = "MyTable"
MsgBox (v_MyTable.Rows.Count)

以下给出了错误:

对象变量或未设置带块变量

Object variable or With block variable not set

v_MyTable_b = "[" & "MyTable" & "]"
MsgBox(v_MyTable_b.Rows.Count)

我还尝试使用ListObjects，这是我的新手.我收到错误消息:

I also tried working with ListObjects, which I am new to. I get the error:

对象不支持此属性或方法

Object doesn't support this property or method

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("MyTable")
MsgBox(tbl.Rows.Count)

感谢您的帮助！

推荐答案

您需要对检索的内容进行更深入的研究.

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

更多信息，位于:

ListObject接口
ListObject.Range属性
ListObject.DataBodyRange属性
查看全文