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        amp   MyTable   amp      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

User · Answer

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

User · Answer

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

User · 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

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

Examples related to excel

Examples related to vba