Range Resize Issue (VBA)

Posted on 2012-08-26
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

    Author Comment

    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

    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

    Thank you Aebea,

    That works :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This article will show you how to use shortcut menus in the Access run-time environment.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now