Counting Excel Table Rows that are populated with data on VBA

0
votes

So im currently working a table in excel that I have named Table1 with three columns (Column 1, Column 2 and Column 3). Ive been trying to count the used rows or populated rows inside the table using VBA but have had no luck.

Example 1:

UsedRows= Sheets ("Sheet1").ListObjects.("Table1[#Column 1]").UsedRange.ListRows.Count

Example 2 (This One Returns only all available rows)

UsedRows= Sheets ("Sheet1").ListObjects.("Table1[#Column 1]").ListRows.Count

I either want the populated or unpolulated row amount. Either of the two will work just fine. Remember this is a Table so End(xlUp) and End(xlDown) work a little bit different. Ive tried those too but I still get either the total rows available or the cells that are modified which is way more than what I have available.

Thanks for the help in adavanced whoever posts.

2
excelvba
Are you trying to get the last row, or the populated rows? You can use CountA for the latter. – BigBen
Is an unpopulated row one with nothing in it, as opposed to having any blanks? – SJR
@BigBen the amount of populated rows – Daume
@SJR Yes one without any type of data/value in it – Daume
@BigBen I tried the CountA but its says this object doesnt support property or method – Daume

2 Answers

3
votes

Sounds like you can use CountA, like this perhaps:

Dim myColumn As ListColumn
Set myColumn = Sheets("Sheet1").ListObjects("Table1").ListColumns("Column 1")

Dim UsedRows As Long
UsedRows = Application.CountA(myColumn.DataBodyRange)
Debug.Print UsedRows
0
votes

If you don't have blank cells in other rows. The 3 doesn't need to be hard-coded, this is just the number of columns in your table.

Sub x()

Dim r As Range

Set r = ActiveSheet.ListObjects(1).DataBodyRange

With WorksheetFunction
    MsgBox .CountBlank(r) / 3                  'empty rows
    MsgBox (r.Rows.Count - .CountBlank(r) / 3) 'non-empty rows
End With

End Sub