Mass Creation of Named Ranges?

Hey all,

I am trying to setup of a worksheet that has ranges A:F as a named
ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed
that when I try to create a mass quantity of named ranges Excel craps
out on me at around 65407...anyone have any idea as to why this is or
if there is a macro I could run to create a worksheet with 65535 named
ranges using that setup? See example below if your confused...

ROW 1: DB1_1 (Ranges A:F)
ROW 2: DB1_2 (Ranges A:F)
ROW 3: DB1_3 (Ranges A:F)
ROW 4: DB1_4 (Ranges A:F)

|||||||||||||||||||||||||||||

ROW 65536: DB1_65536 (Ranges A:F)

Thank you kindly!

Seems an awful lot of names. Why do you need so many, there must be other
ways?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Hey all,
>
> I am trying to setup of a worksheet that has ranges A:F as a named
> ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed
> that when I try to create a mass quantity of named ranges Excel craps
> out on me at around 65407...anyone have any idea as to why this is or
> if there is a macro I could run to create a worksheet with 65535 named
> ranges using that setup? See example below if your confused...
>
> ROW 1: DB1_1 (Ranges A:F)
> ROW 2: DB1_2 (Ranges A:F)
> ROW 3: DB1_3 (Ranges A:F)
> ROW 4: DB1_4 (Ranges A:F)
>
> |||||||||||||||||||||||||||||
>
> ROW 65536: DB1_65536 (Ranges A:F)
>
> Thank you kindly!
>

I am designing an application with a submit function...program will
also allow for add, edit, and delete information inside of a database
(which is why its being titled DB1_# (# = Cell number)...I imagine by
the time I get done with this there will be like 300,000 named ranges
inside this single workbook alone...I know this sounds crazy but I am
trying to figure out the best way to allow for data extraction and
modification...would be much easier with named ranges...


Bob Phillips wrote:
> Seems an awful lot of names. Why do you need so many, there must be other
> ways?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Hey all,
> >
> > I am trying to setup of a worksheet that has ranges A:F as a named
> > ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed
> > that when I try to create a mass quantity of named ranges Excel craps
> > out on me at around 65407...anyone have any idea as to why this is or
> > if there is a macro I could run to create a worksheet with 65535 named
> > ranges using that setup? See example below if your confused...
> >
> > ROW 1: DB1_1 (Ranges A:F)
> > ROW 2: DB1_2 (Ranges A:F)
> > ROW 3: DB1_3 (Ranges A:F)
> > ROW 4: DB1_4 (Ranges A:F)
> >
> > |||||||||||||||||||||||||||||
> >
> > ROW 65536: DB1_65536 (Ranges A:F)
> >
> > Thank you kindly!
> >

I agree on the concept, I use the same approach myself, but I never needed
300,000!

Are you saying you have 300,000 fields, or is there some other reason for
300,000 (I just can't get my head around 300,000 fields)?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> I am designing an application with a submit function...program will
> also allow for add, edit, and delete information inside of a database
> (which is why its being titled DB1_# (# = Cell number)...I imagine by
> the time I get done with this there will be like 300,000 named ranges
> inside this single workbook alone...I know this sounds crazy but I am
> trying to figure out the best way to allow for data extraction and
> modification...would be much easier with named ranges...
>
>
> Bob Phillips wrote:
> > Seems an awful lot of names. Why do you need so many, there must be
other
> > ways?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Hey all,
> > >
> > > I am trying to setup of a worksheet that has ranges A:F as a named
> > > ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed
> > > that when I try to create a mass quantity of named ranges Excel craps
> > > out on me at around 65407...anyone have any idea as to why this is or
> > > if there is a macro I could run to create a worksheet with 65535 named
> > > ranges using that setup? See example below if your confused...
> > >
> > > ROW 1: DB1_1 (Ranges A:F)
> > > ROW 2: DB1_2 (Ranges A:F)
> > > ROW 3: DB1_3 (Ranges A:F)
> > > ROW 4: DB1_4 (Ranges A:F)
> > >
> > > |||||||||||||||||||||||||||||
> > >
> > > ROW 65536: DB1_65536 (Ranges A:F)
> > >
> > > Thank you kindly!
> > >
>

Bob,

The information I have been asked to form a database with search
functions would require a database of 4 - 5 diffrent worksheets (each
containing 65,000 rows). Due to the VLookup and Hlookup structure this
makes it very difficult to form a database that would make more logical
sense instead of having 5 diffrent pieces of information in 4 - 5
diffrent locations. I thought about using the ActiveCell.Value function
with "Range(ActiveCell.Value).Select" to goto the areas however since I
am not going to be the one to use the program I find that it would be
more stable with 300,000 names (each having a diffrent location to pin
point)...example

DB1_100 - Database 1 Row 100
DB2_225 - Database 2 Row 225
DB3_34445 - Database 3 Row 34,445
DB4_33211 - Database 4 Row 33,211

The database it's self has a internal refrence number (standard 1 -
infinity number that I created) to refrence the information so that
Vlookup will only need to search for a number instead of a string of
numbers (thought this would make it easier to have a delete / edit
function inside the program). I guess everyone's programs inside of
Excel is designed diffrently...I stay away from VB code as much as
possiable but use it quite often...I like to stick to the built-in
functions of Excel versus using custom script for my programs, both get
the job done. If you know of a better way of doing this then by all
means let me know because I have my computer running a macro creating
these names one at a time but it takes like 5 sec each one...5 sec each
name = roughly 30 hours for 1 database with names to be created
successfully...



Bob Phillips wrote:
> I agree on the concept, I use the same approach myself, but I never needed
> 300,000!
>
> Are you saying you have 300,000 fields, or is there some other reason for
> 300,000 (I just can't get my head around 300,000 fields)?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > I am designing an application with a submit function...program will
> > also allow for add, edit, and delete information inside of a database
> > (which is why its being titled DB1_# (# = Cell number)...I imagine by
> > the time I get done with this there will be like 300,000 named ranges
> > inside this single workbook alone...I know this sounds crazy but I am
> > trying to figure out the best way to allow for data extraction and
> > modification...would be much easier with named ranges...
> >
> >
> > Bob Phillips wrote:
> > > Seems an awful lot of names. Why do you need so many, there must be
> other
> > > ways?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hey all,
> > > >
> > > > I am trying to setup of a worksheet that has ranges A:F as a named
> > > > ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed
> > > > that when I try to create a mass quantity of named ranges Excel craps
> > > > out on me at around 65407...anyone have any idea as to why this is or
> > > > if there is a macro I could run to create a worksheet with 65535 named
> > > > ranges using that setup? See example below if your confused...
> > > >
> > > > ROW 1: DB1_1 (Ranges A:F)
> > > > ROW 2: DB1_2 (Ranges A:F)
> > > > ROW 3: DB1_3 (Ranges A:F)
> > > > ROW 4: DB1_4 (Ranges A:F)
> > > >
> > > > |||||||||||||||||||||||||||||
> > > >
> > > > ROW 65536: DB1_65536 (Ranges A:F)
> > > >
> > > > Thank you kindly!
> > > >
> >

Could you not just a lookup function that tests the lookup value and then
retrieves from the appropriate page. As you have very ordered index of 1 to
infinity, you can easily determine which page it is on. You don't even need
VLLOKUP, assuming that the sheets are called Data1, Data2, etc, you could
use

="'Data"&INT(F4/65536)+1&"'!A"&MOD(F4-1,65536)+1


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> The information I have been asked to form a database with search
> functions would require a database of 4 - 5 diffrent worksheets (each
> containing 65,000 rows). Due to the VLookup and Hlookup structure this
> makes it very difficult to form a database that would make more logical
> sense instead of having 5 diffrent pieces of information in 4 - 5
> diffrent locations. I thought about using the ActiveCell.Value function
> with "Range(ActiveCell.Value).Select" to goto the areas however since I
> am not going to be the one to use the program I find that it would be
> more stable with 300,000 names (each having a diffrent location to pin
> point)...example
>
> DB1_100 - Database 1 Row 100
> DB2_225 - Database 2 Row 225
> DB3_34445 - Database 3 Row 34,445
> DB4_33211 - Database 4 Row 33,211
>
> The database it's self has a internal refrence number (standard 1 -
> infinity number that I created) to refrence the information so that
> Vlookup will only need to search for a number instead of a string of
> numbers (thought this would make it easier to have a delete / edit
> function inside the program). I guess everyone's programs inside of
> Excel is designed diffrently...I stay away from VB code as much as
> possiable but use it quite often...I like to stick to the built-in
> functions of Excel versus using custom script for my programs, both get
> the job done. If you know of a better way of doing this then by all
> means let me know because I have my computer running a macro creating
> these names one at a time but it takes like 5 sec each one...5 sec each
> name = roughly 30 hours for 1 database with names to be created
> successfully...
>
>
>
> Bob Phillips wrote:
> > I agree on the concept, I use the same approach myself, but I never
needed
> > 300,000!
> >
> > Are you saying you have 300,000 fields, or is there some other reason
for
> > 300,000 (I just can't get my head around 300,000 fields)?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > I am designing an application with a submit function...program will
> > > also allow for add, edit, and delete information inside of a database
> > > (which is why its being titled DB1_# (# = Cell number)...I imagine by
> > > the time I get done with this there will be like 300,000 named ranges
> > > inside this single workbook alone...I know this sounds crazy but I am
> > > trying to figure out the best way to allow for data extraction and
> > > modification...would be much easier with named ranges...
> > >
> > >
> > > Bob Phillips wrote:
> > > > Seems an awful lot of names. Why do you need so many, there must be
> > other
> > > > ways?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hey all,
> > > > >
> > > > > I am trying to setup of a worksheet that has ranges A:F as a named
> > > > > ranged using the name "DB1_#" (# = Cells 1 - 65535), I have
noticed
> > > > > that when I try to create a mass quantity of named ranges Excel
craps
> > > > > out on me at around 65407...anyone have any idea as to why this is
or
> > > > > if there is a macro I could run to create a worksheet with 65535
named
> > > > > ranges using that setup? See example below if your confused...
> > > > >
> > > > > ROW 1: DB1_1 (Ranges A:F)
> > > > > ROW 2: DB1_2 (Ranges A:F)
> > > > > ROW 3: DB1_3 (Ranges A:F)
> > > > > ROW 4: DB1_4 (Ranges A:F)
> > > > >
> > > > > |||||||||||||||||||||||||||||
> > > > >
> > > > > ROW 65536: DB1_65536 (Ranges A:F)
> > > > >
> > > > > Thank you kindly!
> > > > >
> > >
>

Can you explain the logic behind that code? It's one thing to post the
code but its another to explain it...


Bob Phillips wrote:
> Could you not just a lookup function that tests the lookup value and then
> retrieves from the appropriate page. As you have very ordered index of 1 to
> infinity, you can easily determine which page it is on. You don't even need
> VLLOKUP, assuming that the sheets are called Data1, Data2, etc, you could
> use
>
> ="'Data"&INT(F4/65536)+1&"'!A"&MOD(F4-1,65536)+1
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> >
> > The information I have been asked to form a database with search
> > functions would require a database of 4 - 5 diffrent worksheets (each
> > containing 65,000 rows). Due to the VLookup and Hlookup structure this
> > makes it very difficult to form a database that would make more logical
> > sense instead of having 5 diffrent pieces of information in 4 - 5
> > diffrent locations. I thought about using the ActiveCell.Value function
> > with "Range(ActiveCell.Value).Select" to goto the areas however since I
> > am not going to be the one to use the program I find that it would be
> > more stable with 300,000 names (each having a diffrent location to pin
> > point)...example
> >
> > DB1_100 - Database 1 Row 100
> > DB2_225 - Database 2 Row 225
> > DB3_34445 - Database 3 Row 34,445
> > DB4_33211 - Database 4 Row 33,211
> >
> > The database it's self has a internal refrence number (standard 1 -
> > infinity number that I created) to refrence the information so that
> > Vlookup will only need to search for a number instead of a string of
> > numbers (thought this would make it easier to have a delete / edit
> > function inside the program). I guess everyone's programs inside of
> > Excel is designed diffrently...I stay away from VB code as much as
> > possiable but use it quite often...I like to stick to the built-in
> > functions of Excel versus using custom script for my programs, both get
> > the job done. If you know of a better way of doing this then by all
> > means let me know because I have my computer running a macro creating
> > these names one at a time but it takes like 5 sec each one...5 sec each
> > name = roughly 30 hours for 1 database with names to be created
> > successfully...
> >
> >
> >
> > Bob Phillips wrote:
> > > I agree on the concept, I use the same approach myself, but I never
> needed
> > > 300,000!
> > >
> > > Are you saying you have 300,000 fields, or is there some other reason
> for
> > > 300,000 (I just can't get my head around 300,000 fields)?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I am designing an application with a submit function...program will
> > > > also allow for add, edit, and delete information inside of a database
> > > > (which is why its being titled DB1_# (# = Cell number)...I imagine by
> > > > the time I get done with this there will be like 300,000 named ranges
> > > > inside this single workbook alone...I know this sounds crazy but I am
> > > > trying to figure out the best way to allow for data extraction and
> > > > modification...would be much easier with named ranges...
> > > >
> > > >
> > > > Bob Phillips wrote:
> > > > > Seems an awful lot of names. Why do you need so many, there must be
> > > other
> > > > > ways?
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > >
> > > > > <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hey all,
> > > > > >
> > > > > > I am trying to setup of a worksheet that has ranges A:F as a named
> > > > > > ranged using the name "DB1_#" (# = Cells 1 - 65535), I have
> noticed
> > > > > > that when I try to create a mass quantity of named ranges Excel
> craps
> > > > > > out on me at around 65407...anyone have any idea as to why this is
> or
> > > > > > if there is a macro I could run to create a worksheet with 65535
> named
> > > > > > ranges using that setup? See example below if your confused...
> > > > > >
> > > > > > ROW 1: DB1_1 (Ranges A:F)
> > > > > > ROW 2: DB1_2 (Ranges A:F)
> > > > > > ROW 3: DB1_3 (Ranges A:F)
> > > > > > ROW 4: DB1_4 (Ranges A:F)
> > > > > >
> > > > > > |||||||||||||||||||||||||||||
> > > > > >
> > > > > > ROW 65536: DB1_65536 (Ranges A:F)
> > > > > >
> > > > > > Thank you kindly!
> > > > > >
> > > >
> >

INT is explained in help as is MOD.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Can you explain the logic behind that code? It's one thing to post the
> code but its another to explain it...
>
>
> Bob Phillips wrote:
> > Could you not just a lookup function that tests the lookup value and
then
> > retrieves from the appropriate page. As you have very ordered index of 1
to
> > infinity, you can easily determine which page it is on. You don't even
need
> > VLLOKUP, assuming that the sheets are called Data1, Data2, etc, you
could
> > use
> >
> > ="'Data"&INT(F4/65536)+1&"'!A"&MOD(F4-1,65536)+1
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Bob,
> > >
> > > The information I have been asked to form a database with search
> > > functions would require a database of 4 - 5 diffrent worksheets (each
> > > containing 65,000 rows). Due to the VLookup and Hlookup structure this
> > > makes it very difficult to form a database that would make more
logical
> > > sense instead of having 5 diffrent pieces of information in 4 - 5
> > > diffrent locations. I thought about using the ActiveCell.Value
function
> > > with "Range(ActiveCell.Value).Select" to goto the areas however since
I
> > > am not going to be the one to use the program I find that it would be
> > > more stable with 300,000 names (each having a diffrent location to pin
> > > point)...example
> > >
> > > DB1_100 - Database 1 Row 100
> > > DB2_225 - Database 2 Row 225
> > > DB3_34445 - Database 3 Row 34,445
> > > DB4_33211 - Database 4 Row 33,211
> > >
> > > The database it's self has a internal refrence number (standard 1 -
> > > infinity number that I created) to refrence the information so that
> > > Vlookup will only need to search for a number instead of a string of
> > > numbers (thought this would make it easier to have a delete / edit
> > > function inside the program). I guess everyone's programs inside of
> > > Excel is designed diffrently...I stay away from VB code as much as
> > > possiable but use it quite often...I like to stick to the built-in
> > > functions of Excel versus using custom script for my programs, both
get
> > > the job done. If you know of a better way of doing this then by all
> > > means let me know because I have my computer running a macro creating
> > > these names one at a time but it takes like 5 sec each one...5 sec
each
> > > name = roughly 30 hours for 1 database with names to be created
> > > successfully...
> > >
> > >
> > >
> > > Bob Phillips wrote:
> > > > I agree on the concept, I use the same approach myself, but I never
> > needed
> > > > 300,000!
> > > >
> > > > Are you saying you have 300,000 fields, or is there some other
reason
> > for
> > > > 300,000 (I just can't get my head around 300,000 fields)?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I am designing an application with a submit function...program
will
> > > > > also allow for add, edit, and delete information inside of a
database
> > > > > (which is why its being titled DB1_# (# = Cell number)...I imagine
by
> > > > > the time I get done with this there will be like 300,000 named
ranges
> > > > > inside this single workbook alone...I know this sounds crazy but I
am
> > > > > trying to figure out the best way to allow for data extraction and
> > > > > modification...would be much easier with named ranges...
> > > > >
> > > > >
> > > > > Bob Phillips wrote:
> > > > > > Seems an awful lot of names. Why do you need so many, there must
be
> > > > other
> > > > > > ways?
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (replace somewhere in email address with gmail if mailing
direct)
> > > > > >
> > > > > > <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Hey all,
> > > > > > >
> > > > > > > I am trying to setup of a worksheet that has ranges A:F as a
named
> > > > > > > ranged using the name "DB1_#" (# = Cells 1 - 65535), I have
> > noticed
> > > > > > > that when I try to create a mass quantity of named ranges
Excel
> > craps
> > > > > > > out on me at around 65407...anyone have any idea as to why
this is
> > or
> > > > > > > if there is a macro I could run to create a worksheet with
65535
> > named
> > > > > > > ranges using that setup? See example below if your confused...
> > > > > > >
> > > > > > > ROW 1: DB1_1 (Ranges A:F)
> > > > > > > ROW 2: DB1_2 (Ranges A:F)
> > > > > > > ROW 3: DB1_3 (Ranges A:F)
> > > > > > > ROW 4: DB1_4 (Ranges A:F)
> > > > > > >
> > > > > > > |||||||||||||||||||||||||||||
> > > > > > >
> > > > > > > ROW 65536: DB1_65536 (Ranges A:F)
> > > > > > >
> > > > > > > Thank you kindly!
> > > > > > >
> > > > >
> > >
>