Last row in range

[ErikJan]

If I have a range, e.g. Range("A2:A5000"), how do I get the last row (5000) in a variable? I tried "Specialcells(xlCellTypeLastCell)" but that gave me something like 5348... odd...

[StuartR]

Assume that you have a named range, MyRange, then the last row would be
Range("MyRange").Rows.Count + Range("MyRange").Row - 1

If you have a variable rngHereitis then you can use rngHereitis instead of Range("MyRange") in that example

[HansV]

By the way, Erik Jan, your mail server is bouncing the e-mail notifications of replies to your questions as spam...

[ErikJan]

Assume that you have a named range, MyRange, then the last row would be
Range("MyRange").Rows.Count + Range("MyRange").Row - 1

If you have a variable rngHereitis then you can use rngHereitis instead of Range("MyRange") in that example

Works, thank you

[ErikJan]

By the way, Erik Jan, your mail server is bouncing the e-mail notifications of replies to your questions as spam...

Thanks Hans, working on it... if you reply to this one (as you know ;-))

[HansV]

OK, here you go.

This is a test to see if e-mail notifications are still rejected by your mail server.

[HansV]

Still bouncing. I've sent you a PM with the text of one of the failure messages.

[ErikJan]

Should be fixed now... I added the sending address to a "Senders Whitelist" in my Spam filter. Thanks again for letting me know the emails bounced...

[HansV]

Let's see - do you get a notification of this reply?

[ErikJan]

Yes

[HansV]

Good to hear that. Thanks for the confirmation.