dynamic name ranges 1

[Squibbles]

Hi,
I am trying to define a range in Excell, dynamically. Currently I am using the following:

Insert>Name>Define box -

sourcedata =OFFSET(Sheetname!$A$1,0,0,COUNTA(Sheetname!$A:$A),COUNTA(Sheetname!$1:$1))

But my main problem is that each week there is new data, in which the size of the columns vary, sometime col(G) is the largest column and so on. Ideally I need to know how to define cells(1,1).currentregion (from vba code) in the Defin dialog box.

Any suggestions?

Kind Regards,

Squibs

 

[SkipVought]

Hi,

It's MORE than knowing which column is the "largest"

It's -- which column contains ONE VALUE FOR EVERY ROW.

So unless you know that, OFFSET won't work properly.

You could make a function to return the number of rows in the CurrentRegion, and stuff that in the 4th argument.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Squibbles]

oo.. how can i make a function that retunrs the number of rows in the CurrentRegion?

Cheers,
Squibs

 

[SkipVought]

function CurrentRegionRows(rng as range) as long
   CurrentRegionRows = rng.currentregion.rows.count
end function

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Gavona]

If you are running your code each week when there is new data then can't you simply name the range in code:

cells(1,1).currentregion.name="sourcedata".

The name will then be available in the name box on the formula bar - which can be useful.
Won't work if users add columns to the right of the last column or rows below the last row between times when you run your code but if they insert new rows/columns within your named range then that will be fine.

Thanks,

Gavin

 

[SkipVought]

Gavin,

That's why the OFFSET function works so well to create a DYNAMIC named range.

Trouble is in Squibs data, there is no one column that contains a value in each row. Hence the UDF to use in the OFFSET function.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Gavona]

Cheers Skip. I do use dynamic named ranges and agree that they are really useful. Sometimes though they scare other users of the workbook so I try to avoid using them unnecessarily.


Thanks,

Gavin