Simple way to count the used rows in a column

[Glory]

These all fail

MsgBox ActiveSheet.Columns("A").UsedRange.Count
MsgBox ActiveSheet.Columns("A").Cells.UsedRange.Count
MsgBox ActiveSheet.Columns("A").Rows.UsedRange.Count

 

[VoG]

Try

MsgBox Range("A" & Rows.Count).End(xlUp).Row

 

[schielrn]

Maybe?

msgbox worksheetfunction.counta(activesheet.columns("A"))

but this will only count cells that have data in them and not include cells that are blank.

 

[Zakkaroo]

    MsgBox Application.WorksheetFunction.CountA _
        (Range("A1", Range("A" & Rows.Count).End(xlUp)))

 

[Glory]

Thanks guys.

VoG: VBA's help says this about the "Row" property:

"Returns the number of the first row of the first area in the range."

Emphasis added. Why specify the "first area"? Why not just say "first row in the range"?

 

[VoG]

In the sense that I used the Row property, they mean the same thing.

 

[Weaver]

Thanks guys.

VoG: VBA's help says this about the "Row" property:

"Returns the number of the first row of the first area in the range."

Emphasis added. Why specify the "first area"? Why not just say "first row in the range"?

consider the code:

Sub test()
    Set rng = Range("A10:C20,F5:G10")
    Debug.Print rng.Row
End Sub

You could argue that the answer should be 5 as this is the first row in the range. However, since the range consists of 2 "areas", the code returns 10, which is the first row of the first area.

 

[Boller]

MsgBox ActiveSheet.UsedRange.Rows.Count

Or perhaps you are looking for :-

MsgBox Range([A1], ActiveSheet.UsedRange).Rows.Count