VBA Count Table Rows which Contain data

Question

Follow on from earlier question. I am trying to count the number of rows in a table which contain data (not counting number of rows which are part of the table).

For example, my table is:

My code is:

With ThisWorkbook.Worksheets(1)
    Set ATB = .ListObjects("Table1")
    .Activate
    numberRows = .Cells(.Rows.Count, "A").End(xlUp).Row
End With`

This returns the wrong number (trust me that column A has the same data count)

Likewise, I want to use VBA to resize the table nicely but need this row count for that.

`.Cells(.Rows.Count, "A").End(xlUp).Row` the parent there is the worksheet, not your table. — findwindow, Apr 19 '16 at 22:14
Use this line instead `numberRows = .Range("A:A").Cells.Find("*", , , , xlByRows, xlPrevious).Row`. If you want more information then see [HERE](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — Scott Craner, Apr 19 '16 at 22:17

score 0 · Answer 1 · answered Apr 20 '16 at 03:42

0

Try this

Sub check()
    With ThisWorkbook.Worksheets("Sheet1")
        Set ATB = .ListObjects("Table1")
        .Activate
        numberRows = Application.CountA(.Range("A:A"))
    End With
End Sub

answered Apr 20 '16 at 03:42

Karthick Gunasekaran

2,697
1
15
25

score 0 · Answer 2 · answered Apr 20 '16 at 04:17

0

Try this excel formula

=COUNTA(Table1[A1])

answered Apr 20 '16 at 04:17

Danielle

317
2
10

VBA Count Table Rows which Contain data

2 Answers2