Range Resize Issue (VBA)

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
Who is Participating?
Anthony BerenguelCommented:
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.
RayneAuthor Commented:
like ....
Set rr = Range("myRange").Cells(1, 1)

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

Debug.Print Range("myRange").Address

still Not getting it....
RayneAuthor Commented:
Thank you Aebea,

That works :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.