View Full Version : Solved: Find the last row in a range
Tezzies
10-08-2008, 03:42 AM
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
10-08-2008, 03:52 AM
Try this, it counts rows in a named range in this case "MyRange"
x = MsgBox(Range("MyRange").Rows.Count)
Hope this helps
georgiboy
10-08-2008, 04:29 AM
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
10-08-2008, 04:56 AM
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
10-08-2008, 05:46 AM
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
10-08-2008, 05:51 AM
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
10-08-2008, 09:55 AM
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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.