Excel resize range via VBA

Rayne used Ask the Experts™

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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
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


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