Name an area that changes
 

Greetings,
I have a column (AO) that I dump a lot of data into and then I name the data
"Dept". The data that I dump in the column changes every week so the number
of rows changes each week so I need to rename the field. What code can I run
to select the data and then name it? Thank you,
Mike

Name an area that changes
 

You could use a dynamic named range
(http://contextures.com/xlNames01.html#Dynamic) or code something like this:

Dim eRow As Long
eRow = Cells(Rows.Count, 41).End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Dept", _
RefersToR1C1:="=Sheet1!R1C41:R" & eRow & "C41"

Regards
Rowan

Mike R. wrote:
Greetings,
I have a column (AO) that I dump a lot of data into and then I name the data
"Dept". The data that I dump in the column changes every week so the number
of rows changes each week so I need to rename the field. What code can I run
to select the data and then name it? Thank you,
Mike

Name an area that changes
 

Mike R:

Try

ActiveWorkbook.Names.Add Name:="Dept", RefersTo:= _
"=OFFSET(sheet1!$AO$1,,,COUNTA(sheet1!$AO$1:$AO$65 536),1)"
MsgBox Range("Dept").Address
Range("Dept").Select

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Mike R." wrote:

Greetings,
I have a column (AO) that I dump a lot of data into and then I name the data
"Dept". The data that I dump in the column changes every week so the number
of rows changes each week so I need to rename the field. What code can I run
to select the data and then name it? Thank you,
Mike