So far, so good, but we still have one instance of IC in there and I'm not
familiar enough with the split function to know how to put that in there
instead (just after central_benefit in the names.add). Can you help? Thanks
again and again!

"Bob Phillips" wrote:

> 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" <Robin@discussions.microsoft.com> wrote in message
> news:5029061B-AFB7-482C-A8EE-DAF0BA8207C9@microsoft.com...
> > 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. :-)
> >
> >
>
>
>