Limitations of Named Ranges

[Gettingbetter]

Hello everybody

I currently have a spreadsheet that has a list of projects ( lets call them Project A, Project B, etc..)

Each time a project date or size is changed, the project needs to be completely removed and then reinput using the new information.

The problem I had was that information about the project is scattered on different dates on a sheet ( These could be any date which was specified when the project was put on )

Because of the size of data in this sheet 3845 rows 72 columns, doing a find and replace takes forever.

To get around this ( With help from this forum ) I used named ranges. This worked that everytime new information was added concerning a project it would be added to a named range, Seebelow

ActiveWorkbook.Names.Add Name:="Project1", RefersTo:="=" & Union([Project1], ActiveCell.Range("A1:B1")).Address

Then if the project was changed I would, use Application.Goto Reference:="Paul1" and clear contents

This worked perfectly, or so I thought

However nightmare struck me last night when my projects started getting bigger and I realised that named ranges can only be so big..

Can anyone think of an alternative way of doing the above??

I hope this makes sence

Cheers

 

[Gettingbetter]

As anyone had a chance to look at this, Im desperate


Cheers

 

[shades]

However nightmare struck me last night when my projects started getting bigger and I realised that named ranges can only be so big..

Can anyone think of an alternative way of doing the above??

Actually the named ranges can be whatever memory can provide. The limiation is non-contiguous ranges within the named range. Perhaps you could have the original set of data as a named range (ProjA), then have any additions to it as another similarly named ranges (i.e. ProjA1, ProjA2, etc.), then the complete set would be the union of those ranges. You could then use a Loop/For Each with i as variable, and do ProjAi.

Just a thought.

 

[Gettingbetter]

Hi Shades

Thanks for the reply

Is there a way of seeing when the Named range has reached its limit?

a sort of If ActiveWorkbook.Name:="Project1".size = etc...

Or will I have to use the on error function?

Cheers