Counting number of rows in table

[mst3k4L]

I'm not sure why this is being so difficult, but for some reason I can't seem to count the number of rows in a given table. I keep getting an error which states:

Run-time error '424':
Object required

Any help is appreciated. Code is below, and I'm using Excel 2007. Thanks in advance.

Sub QC()
    Dim TableRows As Integer
    
    TableRows = Activeworksheet.Tables("CAPEX").Rows.Count
    
End Sub

 

[Ron Coderre]

Excel "tables" are ListObjects...(also: there is no "activeworksheet"...use this: ActiveSheet)

Try this:

Sub QC()
    Dim TableRows As Integer
    
    TableRows = ActiveSheet.ListObjects("CAPEX").Range.Rows.Count
End Sub

Edited to include this alternative structure:

Sub QC1()
    Dim TableRows As Integer
    TableRows = [CAPEX[#All]].Rows.Count
    MsgBox TableRows
End Sub

Is that something you can work with?

 

[mst3k4L]

Excel "tables" are ListObjects...(also: there is no "activeworksheet"...use this: ActiveSheet)

Try this:

Sub QC()
    Dim TableRows As Integer
    
    TableRows = ActiveSheet.ListObjects("CAPEX").Range.Rows.Count
End Sub

That did it! Thanks.