Renaming Ranges

waggy30

New Member
Joined
Mar 2, 2002
Messages
46
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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

Code:
Sub CreateNamedRange()
ActiveWorkbook.Names.Add "AnyNameYouLike", "=" & ActiveSheet.Name & _
                "!" & Selection.CurrentRegion.Address, True
End Sub
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
No, Russell the first row of the actual data is F25:H25. Would it still work if i want multiple columns?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top