Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
1.3k views
Welcome To Ask or Share your Answers For Others

1 Answer

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share

548k questions

547k answers

4 comments

86.3k users

...