Macro Beginner - Help Selecting / Naming Changing Range

[datadrvn]

I promised I've tried seaching before asking...I'm trying to select a range of data so I can assign it a range name. I often import data where the number of columns and rows change and not every cell has data present.

I use the command "Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select" to highlight the desired section, which adjusts to the changing data nicely. The problem comes when it's time to name the range, Excel records the activity as:

ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:= _
"='CF Open Migr CQs - 2010-03-10b'!R1C1:R38C22"
ActiveWorkbook.Names("test").Comment = ""

Unfortunately, this limits the name to the range I had selected at the time. When I run the macro a second time, if the range is different, the macro doesn't care as it has the previous range.

Help is always appreciated.

Wayne
Excel 2007

 

[Joe4]

Give this a shot:

ActiveWorkbook.Names.Add Name:="test", RefersTo:=Range(Selection, ActiveCell.SpecialCells(xlLastCell))

 

[Norie]

Wayne

You shouldn't need to use Select/Selection for this.

It's also a bit easier to name a range with something like this.

Worksheets("Sheet1").Range("A1:C10").Name = "test"

Obviously you would change the sheet name and range as needed.

If you can give some more information about the range you want to name then perhaps someone can post something more specific.

 

[datadrvn]

Never ceases to amaze me how everyone is so helpful. Thank you...this worked perfectly. I never thought of incorporating the selection in the name definition. I tried selecting first and then naming, because that's how I do it with the UI. I have so much to learn.

Thanks again.