How to add Range Selection of an Active Shape's Top/Bottom Row

Hello,


I'm trying to make a formula that gathers the top/bottom row # of a marco button and transfers the row #'s into Range command.

I've got this to work in a "MsgBox" format, but when trying transfer the same formula to a range select command, I seem to be out of my depth.

Please Login or Register  to view this content.

Column Range is a constant D:K, but row numbers are dynamic and would vary throughout the document.

Any help would be appreciated. Thanks!

Rory thank you so much for your help. Much appreciated! I posted on Ozgrid more than two weeks ago and all I got were some angry comments that were not all at helpful.

If anyone requires the full macro to capture the top and bottom row positions of an Active Shape (macro button) and place their row values into a range select function, it's below.

Attached as well is a working spreadsheet showing different sizes and position of copy with both macros present.

Range-Select-Active Shape-Position.xlsm

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks again to Rory for not one, but two creative and extremely quick solutions!

Cheers :-)