Hi All,

Just found the problem. There was a 'Workbook_Open' event that defined the
range on opening therefore overwriting the formula derived range.

Sorry if anybody has spent any time on this.

Regards

Michael Beckinsale



"Michael Beckinsale" wrote in message
...
Hi All,

I have a spreadsheet with some 40 named ranges that l want to be dynamic
to use in UserForms and have therefore used the following type of formula
to define the named range

MyNamedRange=offset(Sheet1!$A$1,0,0,COUNTA(Sheet1! $A$1:$A$50),2)

All works fine until l save the workbook. When l re-open the workbook and
look at the defined name it reverts to something like

MyNamedRange = Sheet1!$A$1:$A$11 where A11 would be the last
cell with anything in it

Does anybody know what is wrong. I need to have the named ranges dynamic
for any session that the workbook is used.

I am using Excel 2003 but it needs to work in Excel 97

TIA

Regards

Michael Beckinsale