Solved: Find the last row in a range

[Tezzies]

my range is for example $K$5:$P$189 . I want to display 189 in a msgbox

i tried

myrange = "$K$5:$P$189"

msgbox sheet1.range(myrange).end(xldown)

but this gives me the last row in the whole sheet

any ideas guys

[georgiboy]

Try this, it counts rows in a named range in this case "MyRange"

x = MsgBox(Range("MyRange").Rows.Count)
Hope this helps

[georgiboy]

Verry crude but it works

Sub d()

Dim rCell As Range, MyRange As Range

Set MyRange = Range("$K$5:$P$189")

For Each rCell In MyRange.Cells
rCell.Activate
Next

MsgBox ActiveCell.Row


End Sub

im sure someone will have a better way but this might do

hope this helps

[georgiboy]

And finally here it is

Sub CountRange()

MyRange = ("$K$5:$P$195")

MsgBox (Range(MyRange).Cells.Row - 1 + (Range(MyRange).Rows.Count))

End Sub

Hope this helps:banghead:

[mdmackillop]

Sub LastRow()

Dim myrange as string
myrange = ("$K$5:$P$195")

MsgBox Range(Split(myrange, ":")(1)).Row
'or
MsgBox Split(myrange, "$")(UBound(Split(myrange, "$")))

End Sub

[Tezzies]

Many thanks, you have reassured me that there is no straight forward one liner to solve the problem. i was worried that using 3 lines of code or so might be over kill.

Thanks again for your time guys

[mdmackillop]

In your question, you define the last cell in your code, so the last row is in any case obvious and you don't need more code to find it. Are you trying to find the last row of a dynamic range?