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]

On 2002-03-09 12:46, waggy30 wrote:
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

Sure,

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

is applying the name "Waggy30" the range where the top left cell is A1 and the bottom right cell is the last cell in column C. The statement Range("C65536").End(xlUp).Address is equivalent to going to C65536, pressing the END key, and then pressing the ARROW UP key. The last part (.Address) simply returns the address of that cell.

Is that explanation sufficient?

 

[Barrie Davidson]

PS - based on your information, you could use this:

Range("F25", Range("H65536").End(xlUp).Address).Name = "Waggy30"

 

[waggy30]

Thanks for that. I managed to get it to work now.

 

[waggy30]

yep, thats what i did

 

[Ivan F Moala]

On 2002-03-09 12:46, waggy30 wrote:
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

Barry
Hope you don't mind

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

"A1" is the Top left cell address of your Rg
Range("C65536") is the LAST address of the
Bottom right of your COLUMN so if your last
column is @ z then change Z for c....

Ivan

 

[Guest]

On 2002-03-09 11:23, Paul B wrote:
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

The formula I wrote for the name,

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

Works only for a range starting in A1 and extending down/across. If you have a range starting in column C, you can change the first 2 arguments to your C column, but the 3rd argument that is given,

COUNTA(Sheet2!$1:$1)

counts the number of columns in row 1 that have a value. If you know exactly how many columns your data has but just want your range to extend downward when you enter new data, you can just put the number of columns for this argument, something like this (if your data has 3 columns, or columns C, D, and E):

=OFFSET(Sheet2!$C$1,,,COUNTA(Sheet2!$C:$C),3)

Hope this helps (and this should help you too, Waggy),

Russell