dynamic range by column index

**the666bbq** · 03-14-2011, 11:33 AM

Hi,

I define a dynamic range like this one

=OFFSET(sheet!$J$2;0;0;COUNTA(sheet!$J:$J)-1,1)

however, I have a variable number of columns in that sheet so it could be column "J" but it could as well be column "Q".

How can I replace these fixed columns to a "by column index" reference (for instance column 22) ??

**NBVC** · 03-14-2011, 11:46 AM

Perhaps?

=INDEX(Sheet!$1:$65536;2;22):INDEX(Sheet!$1:$65536;COUNTA(INDEX(Sheet!$1:$65536;0;22));22)

where the 22's represent column 22.

**the666bbq** · 03-14-2011, 12:03 PM

how long would the script last with 65K ??

we have found a solution this way (30 is the col number in this example) :

colHere = Split(ActiveSheet.Cells(1, 30).Address, "$")(1)

ThisWorkbook.Names.Add Name:="rngTest", _
RefersTo:="=OFFSET(theSheet!$" & colHere & "$2,0,0,COUNTAtheSheet!$" & colHere & ":$" & colHere & ")-1,1)"

in the named ranges collection this is translated to "AD" which is indeed column 30.

Don't know if it wins beauty contests though ;-)
Does this seem ok to you ?? Is there a better way to do it ??