Extending a Defined Excel Range

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

Example for Excel VBA

Code:

---

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

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

Code:

---

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

Have you tried to ReDim your range?

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.

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

Check out the Workbook Names object/collection.

Hint: RefersToRange, RefersToR1C1, etc...



td.

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]

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

Code:

---


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

---

coz it doesn't work for me

Any help would be appreciated

thx
Steve

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

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 :-)