Dynamic named ranges not saving

[Michael Beckinsale]

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

 

[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

 

[Tom Ogilvy]

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