Range Name in VBA (XL2K SR-1)

Topic

New Reply

G’day all, I’m back after being unemployed for 4 months. God it feels good to be working again.

Problem, How to define a Range name using VBA, which changes shape each time data is added. I have no problems defining the actual range using xldown etc, but have problem with the following, which was created using the Record Macro function.

ActiveWorkbook.Names.Add name:=”dbase”, RefersToR1C1:= _
“=’REW EFF (2)’!R3C1:R24C13”

How can I set a variable which contains the range, to replace “RefersToR1C1:= “=’REW EFF (2)’!R3C1:R24C13”

I have played around with ‘With Selection’ and setting a variable to the range, but I get a Range Name with = TRUE, or a syntax error. VBA HELP was not helpful, guess I did not know what to look up.

Hope someone can help

Reply | Quote

Replies

I’m not positive exactly what you are asking, but will this get you started:

Dim strAddress As String
    strAddress = """='REW EFF (2)'!R3C1:R24C13"""

Reply | Quote

Not exactly.

Every time I run the code, the range dimension will have changed. I need a method of redefining the range, each time I run the code. But I can’t work out how to feed the range to the names command.

Reply | Quote

If the object variable oRange is set to the range in question, then this should work:

Dim strAddress As String
    strAddress = """='" & oRange.Parent.Name & "'!" & oRange.Address(True, True, xlR1C1) & """"

Reply | Quote

Not sure how to use that, here is my code. Please advise how you would use the variable to replace the “RefersToR1C1” argument

Dim rn As Variant
‘
Worksheets(“REW EFF (2)”).Activate
Range(“A3″).Select
Selection.End(xlDown).Select
rn = ActiveCell.Row
Range(Cells(3, 1), Cells(rn, 13)).Select
ActiveWorkbook.Names.Add name:=”dbase”, RefersToR1C1:= _
“=’REW EFF (2)’!R3C1:R24C13”

Reply | Quote

Like this:

Dim strAddress As String
Dim oRange As Range
    Set oRange = Range(Worksheets("REW EFF (2)").Cells(3, 1), _
      Worksheets("REW EFF (2)").Cells(Worksheets("REW EFF (2)").Range("A3").End(xlDown).Row, 13))
    strAddress = """='" & oRange.Parent.Name & "'!" & oRange.Address(True, True, xlR1C1) & """"

Reply | Quote

Beudy! (spoken with Mick Dundee Accent)

Many thanks.

Reply | Quote

(Edited by Leif on 13-Mar-04 17:06. to update link to http://www.bmsltd.ie/)

Assuming the rows below your range are all empty.

You can define a dynamic range name using this as the refersto formula:

=OFFSET(‘REW EFF (2)’!$A$3,0,0,COUNTA(‘REW EFF (2)’!A:A)-COUNTA(‘REW EFF (2)’!$A$1:$A$2),13)

This avoids having to redefine the range every time.

BTW:

download my name manager from http://www.bmsltd.ie/mvp%5B/url%5D!

Reply | Quote

Thanks Jan, I already use your name utility. Its great.

Reply | Quote

This might not answer your direct question, but I think it answers your problem:

Once you have the range selected, why not use the line:

Selection.Name = "dbase"

It will define the currently selected range as dbase. You don’t need to use the add names collection at all, so don’t need to worry about the refers to.

Steve

Reply | Quote

Without using Selection I sometimes use something like

zLastRow = [a1].currentRegion.Rows.Coiunt
temp = “a1:h” & zLastRow
Range(temp).Name = “dBase”

..where h is the last column in the block etc.

zeddy

Reply | Quote

OOps – typo, should’ve been

zLastRow = [a1].currentRegion.Rows.Count

zeddy

Reply | Quote