Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Range Resize Issue (VBA)

Posted on 2012-08-26
Medium Priority
Last Modified: 2012-08-26
Hello All,

I have a range resize issue. Something happens with the elements lying in  E5 : K5. After that something happens, I need to resize the horizontal range to include D5.

So myRange before was was E5:K5
But after the resize, it becomes D5:K5

How can this be done in VBA?

Thank you
Question by:Rayne
  • 2

Author Comment

ID: 38335428
like ....
Set rr = Range("myRange").Cells(1, 1)

Set rr = rr.Offset(0, -1)

Debug.Print Range("myRange").Address

still Not getting it....
LVL 10

Accepted Solution

Anthony Berenguel earned 2000 total points
ID: 38335441
Rayne, as long as you know what is going to trigger the change of the name range you can place this just after that code...
ActiveWorkbook.Names.Add Name:="myRange", RefersToR1C1:= _
        "=Sheet1!R5C4:R5C11" 'where R5C4 represent row 5, column 4 (cell D5), etc. 

Open in new window

of course you will have to mind the sheet name and adjust as necessary.

Author Comment

ID: 38335455
Thank you Aebea,

That works :)

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question