ListObject Table Row Count
I have an excel table with one header line and one data body line. I want to count the rows of a data body. When I try to check how many rows my table has with
Set myWorkSheet= ActiveWorkbook.Worksheets("Sheet1")
Set myTable= myWorkSheet.ListObjects("Table1")
countrows = myTable.ListRows.Count
countrows
contains 0
. If it has 2 or more lines, it gives the right line number. Why does it say 0 for one ans line, is it the best way to count the lines, or better?
EDIT:
Found what is causing the problem. I use these lines to clear the table before filling it up again:
If tblChart.ListRows.Count >= 1 Then
myTable.DataBodyRange.Delete
End If
After this operation, the table looks as I described it. Without it and changing the table to look like what I described the table in which it worked. But why is this causing this problem?
source to share
The ListObject property you are looking for is .DataBodyRange
.
Dim myWorkSheet As Worksheet, myTable As ListObject, countRows As Long
Set myWorkSheet = ActiveWorkbook.Worksheets("Sheet1")
Set myTable = myWorkSheet.ListObjects("Table1")
countRows = myTable.DataBodyRange.Rows.Count
Debug.Print countRows
A complete list of ListObject properties is available at: ListObject Interface .
source to share
Answering at the end of a google search, user here: I found this problem too. The DataBodyRange does not exist unless there are two rows of data. Not only two blank rows, but also two rows of data in at least one column. What I found to work reliably is to check if the DataBodyRange exists:
If Not TableListObject.DataBodyRange is Nothing Then
Debug.Print "Data Rows Count=", TableListObject.DataBodyRange.Rows.Count
Else
Debug.Print "No Data in Table detected"
End if
source to share