Renaming Ranges

[waggy30]

Hello.
I am trying to write some code that deletes a name from a range. then re-assigns that name to another.

More specifically.

A B C
1 2 3
2 2 7
6 7 8
9 8 2

The range of cells above would change each time another macro is run. There may be more or less rows of data. So it could be:

A B C
1 2 3
2 2 7
6 7 8
9 8 2
8 9 4
5 6 4

What i want to do is rename the range each time to account for the new rows.

I have been trying for hours. can anyone help me?

[Barrie Davidson]

Use:

Range("A1", Range("C65536").End(xlUp).Address).Name = "Waggy30"


Regards,

[dk]

Hi,

This code will add a named range for the current region. Say you had data in range A1:C3 and then you ran the macro it will create a named range AnyNameYouLike referring to that area. Now, if data is added to cell A4, the macro reran it will now refer to A1:C4.

Let me know if that isn't right,

Regards,
D

Sub CreateNamedRange()
ActiveWorkbook.Names.Add "AnyNameYouLike", "=" & ActiveSheet.Name & _
                "!" & Selection.CurrentRegion.Address, True
End Sub

[Matanuska]

Have you considered using the OFFSET and COUNT functions to construct dynamic references that update automatically as data are added?

This sounds like it might do what you want.

[Aladin Akyurek]

On 2002-03-09 08:38, waggy30 wrote:
Hello.
I am trying to write some code that deletes a name from a range. then re-assigns that name to another.

More specifically.

A B C
1 2 3
2 2 7
6 7 8
9 8 2

The range of cells above would change each time another macro is run. There may be more or less rows of data. So it could be:

A B C
1 2 3
2 2 7
6 7 8
9 8 2
8 9 4
5 6 4

What i want to do is rename the range each time to account for the new rows.

I have been trying for hours. can anyone help me?

Another way to achieve what you want.

Lets say that A2:C7 in Sheet1 houses your first sample data (I suppose A1:C1 will house descriptive labels).


Activate Insert|Name|Define.

Enter 'EndRow' in the "Names in Workbook" box.

Enter the following formula in the "Refers to" box:

=OFFSET(Sheet1!$A$2,0,0,MATCH(9.99999999999999E+307,Sheet1!$A:$A),1)

I assume here that data in column A in Sheet1 is of numeric type (numbers or dates). If this is not the case, replace A with the column identifier of another column which meets the condition of being of numeric type.

Click Add.

While still in the Define Name window,

enter SDATA (from Source Data) in the Names in Workbook box;

enter as formula in the Refers to box:

=OFFSET(Sheet1!$A$2,0,0,EndRow-1,3)

Click OK.

-1 in the EndRow-1 bit "strips off" the row that contains labels from the definition of the range.

Try as an exercise:

=VLOOKUP(2,SDATA,3,0)

Try also adding more rows of data or deleting rows from Sheet1.


[ This Message was edited by: Aladin Akyurek on 2002-03-11 08:46 ]

You can also define a dynamic range, which will change as you enter data.

If you go to Insert-Name-Define, type in a name for the range, the type the following formula in the Refers To box:

=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!
$1:$1))

Note: if you data does not start in cell A1, then you will have to adjust this. Let me know if it does not.

Hope this helps,

Russell

[Russell Hauf]

Sorry, the last message was from me.

[Paul B]

Russell,I am trying to use your formula for a name range it works fine for column a I need it for column C, I thought I could just change the numbers on the end but that is not working for me. Also is there any way to paste in the refers to box? Thanks

[waggy30]

No, Russell the first row of the actual data is F25:H25. Would it still work if i want multiple columns?

[waggy30]

Barry, can you explain what your code is doing. So i can adapt it to my real situation (the data isn't in the cells i used an example). I tried your code with the example I gave and it worked, but i can't adapt it.

Thanks