Dynamic named ranges

[Bernieg]

Hi

I'm trying to set up a named range called " chart_op_data "
the code below works fine counting rows & 12 columns.

However there is often less columns then 12 , 12 being the maximum
data is used for charts

how do i count columns as well....i'm sure theres some one who knows

Bernie

' Chart option data
ActiveWorkbook.Names.Add Name:="chart_op_data", RefersToR1C1:= _
"=OFFSET('Column Chart, Options Data'!R1C1,0,0,COUNTA('Column Chart, Options Data'!C1)+1,12)"

 

[Arul.rajesh]

instead of 12 use COUNTA('Column Chart, Options Data'!R1)

 

[Bernieg]

added as you sugested but iget 3 columns extra

ActiveWorkbook.Names.Add Name:="chart_op_data", RefersToR1C1:= _
"=OFFSET('Column Chart, Options Data'!R1C1,0,0,COUNTA('Column Chart, Options Data'!C1)+1,COUNTA('Column Chart, Options Data'!R1))"

sure im doing somthing wrong

Bernie

 

[Bernieg]

it does work i had 3 columns further to the left used for counting

sorry about that

is there a way to limit the column count to say up to 12 columns

Bernie

 

[Bernieg]

Hi

Deleted the 3 columns to the left, but it still leaves me 1 column short

i.e 5 populated columns, but the range only covers 4

Bernie

 

[niceguy_jay]

can you try this

=OFFSET('Column Chart, Options Data'!R1C1,0,0,COUNTA('Column Chart, Options Data'!R1C[-6]:R65536C[-6])+1,COUNTA('Column Chart, Options Data'!R[-15]C1:R[-15]C256))

 

[Bernieg]

Hi

Did not work at all

Cheers
Bernie

 

[Arul.rajesh]

Hi

Deleted the 3 columns to the left, but it still leaves me 1 column short

i.e 5 populated columns, but the range only covers 4

Bernie

The range will have as many columns as there are non blank cells in row1 and always start at a1.

 

[Bernieg]

Hi Arul

thanks for your help, your code is good the problem is a1 was empty
that was why i was 1 column short

once again thanks for your help & patiance.

Bernie