Reassigning a named range...

[Chopsaki]

Ok, here is my problem.

I have a range called "SomeRange" I am pasting records through vba into this range. The records don't fit into the range and I need the expand the range area to cover all the records.
So I know I have 101 columns and 79 rows that are pasted. How do I programitcally assign the new area of the range? How do I convert the 101 into a cell reference? It is all very confusing.

Thanks in advance.

 

[RobBroekhuis]

No problem at all. If you have a variable of type "name" assigned to the range you're interested in, then you can simply reassign the range that is referred to, for example:

set nmSomeRange=activeworkbook.names("SomeRange&quot
...
nmSomeRange.refersto="=sheet1!$B$2:$D$4"

You can also use the referstoR1C1 property, which makes it easier if you're dynamically redefining the range:

nmSomeRange.referstoR1C1="=sheet1!R1C1:R" & format(nrows) _
& "C" & format(ncolumns)

Does that help?
Rob

 

[Chopsaki]

Almost perfect however I know that I have a varying amount of columns. So how do I translate the numerical value into a column? Like a to 1 and f to 6 etc..

 

[RobBroekhuis]

Use the formulaR1C1 property I suggested above. That way you don't have to give the column in terms of a letter designation - see the example I gave above to generate an R1C1-style reference. If you really want to use the A-IV column designation, the first 26 are easy: a cell reference might be written as:

strRef=chr(64+iColumn) & format(iRow)

from column AA onward, the formula gets messier, so it really is much easier to just use the R1C1 convention.
Rob

 

[Chopsaki]

Sorry I must be slow or something. I still don't understand this. Would it be possible for you to show me with this.
I know the first cell is c1 the amount of columns is x and the amount of rows is y.

How do I let the range refer to that?

Thanks in advance.. sorry for being dumb

 

[RobBroekhuis]

Sure: you'd use

nmSomeRange.referstoR1C1="=sheet1!R1C3:R" & format _
& "C" & format(x+2)

R1C3 is cell C1. The other end of the range is variable, depending on x and y.
Rob

 

[Chopsaki]

OK,, I think I got this working but...

The range seems to disappear afterwards. If I go to insert->names I can see it, but otherwise it is not visible. I am trying to base a chart of this data..

 

[Chopsaki]

OK.. I got it figured out.. Thanks