Dim iLastRow As Long
Dim iLastCol As Long
iLastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
iLastCol = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
ActiveWorkbook.Names.Add "TaxRegime", _
"=Central_Benefit!$IC$1:$" & _
Split(Columns(iLastCol).Address(, False), ":")(1) & _
"$" & iLastRow


--
HTH

Bob Phillips

"Robin" wrote in message
...
I have a formula (written by someone else) that takes a column and makes a
dynamic named range based on how many rows of data there are in a
different
column. The code is:

ActiveWorkbook.Names.Add "TaxRegime", _


"=Central_Benefit!$IC$1:INDEX(Central_Benefit!$IC$ 1:$IC$999,COUNTA(Central_B
enefit!$K$1:$K$999))"

My question is, how can I take this formula and make it dynamic for the
column as well? I want to do a search for the the column header
"tax_regime", then use that instead of saying column IC. (I hope that
makes
sense).

Any help is very much appreciated. :-)