excel - VBA: Counting rows in a table (list object)

Question

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)

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:

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!

Answer 1

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