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!

user4039065 · Accepted Answer · 2015-04-15 00:22:35Z

36

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

answered Apr 15, 2015 at 0:22

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Over a year ago

Note that if the table is empty, tbl.DataBodyRange.Rows.Count throws an error

Kanike Vamshi Krishna Over a year ago

Instead tbl.listrows.count can be used to get the number of rows even if the table is empty. This would not throw any error in a code

Jalal · Accepted Answer · 2018-03-07 08:36:18Z

5

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:

    If WorksheetFunction.CountA(Range("MyTable[#Data]"))

edited Mar 7, 2018 at 8:36

answered Jun 22, 2016 at 13:01

Jalal

6368 silver badges12 bronze badges

3 Comments

Mike Cluck Over a year ago

How does this improve on the accepted answer from over 1 year ago?

Thiago Conrado Over a year ago

Well, I'm sorry that @Jalal anwser was late for you, but I'm really glad he posted it!

Jalal Over a year ago

[#data] can be omited. i.e. Range("MyTable").Rows.Count can be used. This is even better: Range("MyTable").ListObject.ListRows.Count as it will return 0 if the table has no rows (a variation of what @Kanike mentioned in his comment above).

Sebastien D · Accepted Answer · 2018-02-28 19:18:33Z

1

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

edited Feb 28, 2018 at 19:18

Sebastien D

4,5024 gold badges23 silver badges50 bronze badges

answered Feb 28, 2018 at 17:01

Sangamesh B M

111 bronze badge

Collectives™ on Stack Overflow

VBA: Counting rows in a table (list object)

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related