Excel resize range via VBA

Rayne
Rayne used Ask the Experts™
on
Hello,

I have a named range – first_Range. I have two buttons –

Abbreviate - restores the named range to B3:C7
Expand – expands the range to include B3:C11

Now recording macro and doing this doesn’t solves the problem as I am having issues in the vba code.
Is there a VBA code that does that – resizing the range according to the button pressed…
For example, ihis one is not working….

Worksheets(Sheet2).(Range("first_Range").Rows.Count, 1).Resize.Name = "first_Range"


Thank you
resizeVBA.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Commented:
How about this?
Debug.Print Range("first_Range").Address

Range("first_Range").Resize(Range("first_Range").Rows.Count+4).Name = "first_Range"
Debug.Print Range("first_Range").Address

Open in new window

Author

Commented:
How about…. I say that your code works great :) Thank you Sir

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial