Edit VBA code to select last cell in named range

**rocksan** · 07-11-2012, 02:46 AM

Hi everyone,

I named a range [mranges] with offset formula to make it dynamic, now i need vba code so that selects the last cell in [mranges].

Sub Method2()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("Data")
[mranges].Select
End Sub

The last last cell in [mranges] is used to condition another macro.

P.S
If anyone could also edit the vba so that it shows the address and value of the last cell in [mranges] via msgbox that would be an extra.

Thanks.

**TMS** · 07-11-2012, 03:04 AM

Example:

Sub test()

x = Range("mRange").Address
y = Range("mRange").Cells.Count
Z = Right(x, Len(x) - InStr(x, ":"))

MsgBox Range(Z).Address & " " & Range(Z).Value

End Sub

Regards, TMS

**TMS** · 07-11-2012, 03:09 AM

Combining all that ...

MsgBox Range(Right(Range("mRange").Address, Len(Range("mRange").Address) - InStr(Range("mRange").Address, ":"))).Address & " " & Range(Right(Range("mRange").Address, Len(Range("mRange").Address) - InStr(Range("mRange").Address, ":"))).Value

I suspect there may be a better/shorter way but I can't think of it off the top of my head.

Regards, TMS

**rocksan** · 07-11-2012, 03:26 AM

Thanks for the quick reply.

I tested your code, and it works. The msgbox is just to see whether i got the right cell.

Your code got the address and value correct.

But I just want to select the address in macro.

Something like

 Range(Z).Address.select

I tried, but that only results in invalid qualifier error. Anyway can you please edit it so that the macro selects the cell that contains that value.

Thanks

**TMS** · 07-11-2012, 03:41 AM

Range(Range(Right(Range("mRange").Address, Len(Range("mRange").Address) - InStr(Range("mRange").Address, ":"))).Address).Select

Regards, TMS

**rocksan** · 07-11-2012, 03:53 AM

Cool your code works flawlessly.

Also ive found another way to do it,

[mranges].End(xlToRight).Select

Three times today I got my query answered in this forum. Three different people.

Thank you very much TMShucks.

Closing the thread now.

**TMS** · 07-11-2012, 04:34 AM

You're welcome. Thanks for the rep.

I knew there would be a shorter way ... thanks for sharing.

Is your named range just on one row? Your solution seems only to get the last cell on the first row of the named range.

If your range is rectangular, maybe: [mrange].End(xlToRight).End(xlDown).Select ... although that won't work if it IS a single row.

Still shorter ...