dynamic range question

[Guest]

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_Benefit!$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.

 

[Bob Phillips]

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

 

[Guest]

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!

 

[Guest]

I got it. Thanks again for your help.

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

 

[Bob Phillips]

LOL. I thought you wanted to start at IC regardless. Caused some oddities in
my test, because I ended before IC.