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

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

I couldn't reproduce it. I suggest somewhere in your code you are redefining
the named range.

set rng = Range("MyNamedRange")

..
..
..

rng.Name = "MyNamedRange"

as an example

--
Regards,
Tom Ogilvy


"Michael Beckinsale" wrote:

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