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].

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Regards, TMS

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

Combining all that ...

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Regards, TMS

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

Cool your code works flawlessly.

Also ive found another way to do it,

Please Login or Register  to view this content.

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 ...