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