Search code examples
excelvbacountvstolistobject

ListRows.Count Returns Inconsistent Results

I have a strange problem with two of my Excel Tables residing on two different worksheets in my project. I am using VSTO but VBA shows the same result: an empty table's row count has 0 rows in one case and 1 row (I presume the insert row) in another case.

The Setup

Two worksheets: Sheet1, Sheet2
Two corresponding named Excel Tables: Sheet1Table, Sheet2Table

Both tables are empty, i.e. they have one empty row, which is insert row that cannot be deleted.

I run the following code to determine the number of data rows (i.e. excluding the header row):

Microsoft.Office.Tools.Excel.ListObject sheet1Table = Globals.Sheet1.Sheet1Table;
int numberOfListRows1 = sheet1Table.ListRows.Count;

and

Microsoft.Office.Tools.Excel.ListObject sheet2Table = Globals.Sheet2.Sheet2Table;
int numberOfListRows2 = sheet2Table.ListRows.Count;

The result is that numberOfListRows1 is 1 and numberOfListRows2 is 0 although the result (whichever is correct) should be the same. I compared the table and worksheet properties, as well as the source files in Visual Studio, and I could not spot any differences. Any idea what I should be looking for (and which result is the correct one)?

Solution

  • In VBA I used these subs to test your case:

    Sub Sheet1TableRowsCount()
    Dim numberOfListRows1 As Integer
    Dim sheet1Table As ListObject
    Set sheet1Table = Sheet1.ListObjects("Sheet1Table")
    numberOfListRows1 = sheet1Table.ListRows.Count
    Set sheet1Table = Nothing
End Sub

Sub Sheet2TableRowsCount()
    Dim numberOfListRows2 As Integer
    Dim sheet2Table As ListObject
    Set sheet2Table = Sheet2.ListObjects("Sheet2Table")
    numberOfListRows2 = sheet2Table.ListRows.Count
    Set sheet2Table = Nothing
End Sub

    These are the tables:
    table1 table2

    When these tables are created (incorrectly) by selecting the headers and one empty row and then formatting the selection as a table, they have one empty row. This row has no content, COUNTA across the row is 0, and it appears like the insert row that cannot be deleted. It is however a valid data row, so ListRows.Count will get the value of 1.

    Similarly, if you fill in some data and manually delete those entered values (using Delete) so that your tables would look like at the beginning, the results will still be 1.

    If you delete rows manually or programmatically (using something like Sheet1.Range("Sheet1Table").Rows("1").Delete), the ListRows.Count will then yield the value of 0.

    The solution to determining the actual count of data rows is to check ListRows.Count, and if the result is 1 - delete the row after checking if it does not contain actual values. This whole situation can be avoided if an empty table is created by selecting the header row only before clicking Format as Table. The insert row is then created automatically and not counted as a data row (the result of ListRows.Count in this case is 0).