Hi, all!
There is an option in Excel to count all used rows in active sheet:
ActiveSheet.UsedRange.Rows.Count

I would like to count rows in one column, that is shorter then other.
How do I specify certain column to count? Something like:

Columns("M:M").UsedRange.Rows.Count

Reg. VK

this will find the last cell in col M with data
Sub countm()
MsgBox Cells(Rows.Count, "M").End(xlUp).Row
End Sub

--
Don Guillett
SalesAid Software

"VK" wrote in message
...
Hi, all!
There is an option in Excel to count all used rows in active sheet:
ActiveSheet.UsedRange.Rows.Count

I would like to count rows in one column, that is shorter then other.
How do I specify certain column to count? Something like:

Columns("M:M").UsedRange.Rows.Count

Reg. VK

Don Guillett wrote:
this will find the last cell in col M with data
Sub countm()
MsgBox Cells(Rows.Count, "M").End(xlUp).Row
End Sub


That is! Thanks! Is it possible to count rows in column with active
autofilter?
Reg, VK

Have a look at the SUBTOTAL function using the count parameter

--
Don Guillett
SalesAid Software

"VK" wrote in message
...
Don Guillett wrote:
this will find the last cell in col M with data
Sub countm()
MsgBox Cells(Rows.Count, "M").End(xlUp).Row
End Sub


That is! Thanks! Is it possible to count rows in column with active
autofilter?
Reg, VK

Don Guillett wrote:
Have a look at the SUBTOTAL function using the count parameter


Thanks, again! It works!

glad to help

--
Don Guillett
SalesAid Software

"VK" wrote in message
...
Don Guillett wrote:
Have a look at the SUBTOTAL function using the count parameter


Thanks, again! It works!

Don Guillett wrote:
Have a look at the SUBTOTAL function using the count parameter

I overseen, that I'm using
m = Cells(Rows.Count, "I").End(xlUp).Row
Cells(m + 2, 9).Formula = "=Subtotal(9,I2:I94)"

I would like instead of I2:I94

m = Cells(Rows.Count, "I").End(xlUp).Row
Cells(m + 2, 9).Formula = "=Subtotal(9,Cells(2,9):Cells(m,9)"

Of course it doesn't work. How do I specify range in formula dynamically?
Reg, VK

VK wrote:
Don Guillett wrote:

Have a look at the SUBTOTAL function using the count parameter

I overseen, that I'm using
m = Cells(Rows.Count, "I").End(xlUp).Row
Cells(m + 2, 9).Formula = "=Subtotal(9,I2:I94)"

I would like instead of I2:I94

m = Cells(Rows.Count, "I").End(xlUp).Row
Cells(m + 2, 9).Formula = "=Subtotal(9,Cells(2,9):Cells(m,9)"

Of course it doesn't work. How do I specify range in formula dynamically?
Reg, VK


Found:

Cells(m + 2, 9).Formula = "=Subtotal(9,I2:I" & m & ")"

;)