Extending a Defined Excel Range

[Sforster]

Hi all,

I am trying to extend the range of an existing defined range. I have tried to offset the range, resize the range and I just can't get it to work.

Here is what I am trying to do....

Existing range $A$2:$A$42, I want code that will change the range to $A$2:$A$43 and obviously everytime I run the procedure it extends the range by one.

Is there a simple way to do this

Thx
Steve

[paulw]

Example for Excel VBA

iLastRow = 42
strRange = "$A$2:$A$" & Trim$(Str(iLastRow))
ActiveSheet.Range(strRange).Select

will select cells A2:A42, change iLastRow to 43, run again and Hey, Presto!

Make iLastRow some accessible variable and there you are...

Cheers,

Paul

[Sforster]

Thanks for the response Paulw,

What I meant was that if I have a named range, Lets say Locations.

I want to count the number of rows in Locations and then add one to it and then re-define the range that Locations refers to.

Something like

Private Sub UpdateRange()
dim MRange as range, Mcount as byte

Set Mrange = Range("Locations")
Mcount = Mrange.rows.count

blah...blah..blah (off setting by one)

somehow save the named range locations 

end sub

If you get what I mean

thx

Steve

[Confused]

Have you tried to ReDim your range?

[paulw]

Didn't read your post properly, did I?

I am sure I know this but I will have to check my books. Tomorrow OK?

Cheers,

Paul.

[Sforster]

Confused,

What do you mean by Redim my range. Redim is only for arrays as far as I know.

If you know of a way then please post

thx
Steve

[tumblingdown]

Check out the Workbook Names object/collection.

Hint: RefersToRange, RefersToR1C1, etc...



td.

[paulw]

I have been playing around with this and a work round is to use the fact that if you insert a row, the range will extend automatically. This might do what you want - you could put a dummy row in at the bottom and insert there.

I tried deleting the range and re-defining it but the Delete method failed - do you know why?

I am a bit of a novice with Excel, but I am keen to learn...

Let me know if you find a more elegant solution.

Cheers,

Paul

p.s. Hi td, got your mail (at home) but I can't do evenings (sob). I've got two eight week old girls - can get down to you tho'. I'll be in touch.

p.p.s td - Hints are no good, spill the beans...

[Edited by paulw on 11-06-2000 at 11:44 AM]

[Sforster]

Tumblingdown,

Tried that and seem to get errors, could you possibly post some code as to the way in which you mean. Something like this perhaps

activeworkbook.names("Locations").referstolocal = "$A$2:$A$45"

coz it doesn't work for me

Any help would be appreciated

thx
Steve

[Sforster]

Yes, I know what you mean it always fails for me too.

Previously, that is what I have done (inserting a row like you mentioned) but this time, I can't do that and really would like to get to the bottom of this. It seems so easy but I just can't figure out how to do it.

Thx
Steve

[tumblingdown]

Guys.

i assume you have a Named Range "test" already defined (at say Sheet1 A1:A1)

use:

Dim nme As Name

Set nme = ActiveWorkbook.Names("test")
nme.RefersTo = "=Sheet1!$A$1:$C$3"

set nme = nothing



td.

Congrats on your little ones Paul :-)