Hi everyone,

Say I have a block of raws and columns that want to name in a
worksheet using "Box Name".

However, the number of raws is large, or so large, in terms of 100Ks
raws; I don't know what is the max # of raws in Excel new versions.

Yet, I want to use "Box Name" so I use the block name in my work
later. What is the best/smart way to handle this?

Thanks,
Mike

If the range is a single area (like A1:X9999), then the number of rows won't
matter.

If you're trying to use Insert|name|Define (or its equivalent in xl2007), then
there's a limit on the number of characters in the address (256 I _think_ in
xl2003).

But if you're trying to name a bunch of non-contiguous rows (and over 100k
rows/cells), then using a name may not be the best technique.

You may want to explain what you're doing a little more.

Mike wrote:

Hi everyone,

Say I have a block of raws and columns that want to name in a
worksheet using "Box Name".

However, the number of raws is large, or so large, in terms of 100Ks
raws; I don't know what is the max # of raws in Excel new versions.

Yet, I want to use "Box Name" so I use the block name in my work
later. What is the best/smart way to handle this?

Thanks,
Mike

--

Dave Peterson

On Nov 12, 10:43*am, Dave Peterson wrote:
If the range is a single area (like A1:X9999), then the number of rows won't
matter.

If you're trying to use Insert|name|Define (or its equivalent in xl2007), then
there's a limit on the number of characters in the address (256 I _think_ in
xl2003).

But if you're trying to name a bunch of non-contiguous rows (and over 100k
rows/cells), then using a name may not be the best technique.

You may want to explain what you're doing a little more.

Mike wrote:

Hi everyone,

Say I have a block of raws and columns that want to name in a
worksheet using "Box Name".

However, the number of raws is large, or so large, in terms of 100Ks
raws; I don't know what is the max # of raws in Excel new versions.

Yet, I want to use "Box Name" so I use the block name in my work
later. What is the best/smart way to handle this?

Thanks,
Mike

--

Dave Peterson

Dave,

The block is just few columns, about 6 columns, but the number of raws
might range from 10Ks to 100Ks sometimes!? Yes, it is a continuous
single block or single area. So if I have 150K raws, then the block
will be 6 columns by 150K raws which I want to name using Box Name as
"COMBOS"

Usually, I would highlight the block and, in the Box Name, I would
type COMBOS and hit return. Does it mean I should highligh the 6
columns and scrol-down all the way to the last raw allowed, which is
by the way 65536, and then name the block? If so, does it mean if it
turned that I have 100K combos, i.e. 100K raws, it will still be
there?

Thanks,
Mike

Say I have a block of raws and columns that want to name in a
worksheet using "Box Name".

However, the number of raws is large, or so large, in terms of 100Ks
raws; I don't know what is the max # of raws in Excel new versions.

Yet, I want to use "Box Name" so I use the block name in my work
later. What is the best/smart way to handle this?

If the range is a single area (like A1:X9999), then the number of rows
won't
matter.

If you're trying to use Insert|name|Define (or its equivalent in
xl2007), then
there's a limit on the number of characters in the address (256 I
_think_ in
xl2003).

But if you're trying to name a bunch of non-contiguous rows (and over
100k
rows/cells), then using a name may not be the best technique.

You may want to explain what you're doing a little more.

The block is just few columns, about 6 columns, but the number of raws
might range from 10Ks to 100Ks sometimes!? Yes, it is a continuous
single block or single area. So if I have 150K raws, then the block
will be 6 columns by 150K raws which I want to name using Box Name as
"COMBOS"

Usually, I would highlight the block and, in the Box Name, I would
type COMBOS and hit return. Does it mean I should highligh the 6
columns and scrol-down all the way to the last raw allowed, which is
by the way 65536, and then name the block? If so, does it mean if it
turned that I have 100K combos, i.e. 100K raws, it will still be
there?

Just type your address range in the Name Box first in order to select the
range, then type the name you want to give it in the Name Box to name it.
For example, using your initial posts range of A1:X9999.... just type
A1:X9999 in the Name Box and hit the enter key... that range will then be
selected. Now you can Name the range just as if you had manually selected
the cell yourself.

--
Rick (MVP - Excel)

On Nov 12, 11:09*am, "Rick Rothstein"
wrote:
Say I have a block of raws and columns that want to name in a
worksheet using "Box Name".

However, the number of raws is large, or so large, in terms of 100Ks
raws; I don't know what is the max # of raws in Excel new versions.

Yet, I want to use "Box Name" so I use the block name in my work
later. What is the best/smart way to handle this?

If the range is a single area (like A1:X9999), then the number of rows
won't
matter.

If you're trying to use Insert|name|Define (or its equivalent in
xl2007), then
there's a limit on the number of characters in the address (256 I
_think_ in
xl2003).

But if you're trying to name a bunch of non-contiguous rows (and over
100k
rows/cells), then using a name may not be the best technique.

You may want to explain what you're doing a little more.

The block is just few columns, about 6 columns, but the number of raws
might range from 10Ks to 100Ks sometimes!? Yes, it is a continuous
single block or single area. So if I have 150K raws, then the block
will be 6 columns by 150K raws which I want to name using Box Name as
"COMBOS"

Usually, I would highlight the block and, in the Box Name, I would
type COMBOS and hit return. Does it mean I should highligh the 6
columns and scrol-down all the way to the last raw allowed, which is
by the way 65536, and then name the block? If so, does it mean if it
turned that I have 100K combos, i.e. 100K raws, it will still be
there?

Just type your address range in the Name Box first in order to select the
range, then type the name you want to give it in the Name Box to name it.
For example, using your initial posts range of A1:X9999.... just type
A1:X9999 in the Name Box and hit the enter key... that range will then be
selected. Now you can Name the range just as if you had manually selected
the cell yourself.

--
Rick (MVP - Excel)- Hide quoted text -

- Show quoted text -

Could you please clarify how this will be written in the Name Box? Is
it like this "a2:x9999=COMBOS" for example? Walk through
please....Thanks

Go to the name box:
type: A1:X9999
and hit enter.

This selected that large range.

Now while that range is still selected, go back to the name box and type:
COMBOS
and hit enter.

This gives that selected range a name (if that name is not in use already).

ps.

Selecting a range that has tons of rows sometimes means that you're padding the
range with empty cells--just in case their filled in.

If that's the case, you could create a name that grew and contracted based on
the data.

Debra Dalgleish shares instructions for that kind of dynamic name:
http://contextures.com/xlNames01.html#Dynamic



Mike wrote:

On Nov 12, 11:09 am, "Rick Rothstein"
wrote:
Say I have a block of raws and columns that want to name in a
worksheet using "Box Name".

However, the number of raws is large, or so large, in terms of 100Ks
raws; I don't know what is the max # of raws in Excel new versions.

Yet, I want to use "Box Name" so I use the block name in my work
later. What is the best/smart way to handle this?

If the range is a single area (like A1:X9999), then the number of rows
won't
matter.

If you're trying to use Insert|name|Define (or its equivalent in
xl2007), then
there's a limit on the number of characters in the address (256 I
_think_ in
xl2003).

But if you're trying to name a bunch of non-contiguous rows (and over
100k
rows/cells), then using a name may not be the best technique.

You may want to explain what you're doing a little more.

The block is just few columns, about 6 columns, but the number of raws
might range from 10Ks to 100Ks sometimes!? Yes, it is a continuous
single block or single area. So if I have 150K raws, then the block
will be 6 columns by 150K raws which I want to name using Box Name as
"COMBOS"

Usually, I would highlight the block and, in the Box Name, I would
type COMBOS and hit return. Does it mean I should highligh the 6
columns and scrol-down all the way to the last raw allowed, which is
by the way 65536, and then name the block? If so, does it mean if it
turned that I have 100K combos, i.e. 100K raws, it will still be
there?

Just type your address range in the Name Box first in order to select the
range, then type the name you want to give it in the Name Box to name it.
For example, using your initial posts range of A1:X9999.... just type
A1:X9999 in the Name Box and hit the enter key... that range will then be
selected. Now you can Name the range just as if you had manually selected
the cell yourself.

--
Rick (MVP - Excel)- Hide quoted text -

- Show quoted text -

Could you please clarify how this will be written in the Name Box? Is
it like this "a2:x9999=COMBOS" for example? Walk through
please....Thanks

--

Dave Peterson

On Nov 12, 12:04*pm, Dave Peterson wrote:
Go to the name box:
type: *A1:X9999
and hit enter.

This selected that large range.

Now while that range is still selected, go back to the name box and type:
COMBOS
and hit enter.

This gives that selected range a name (if that name is not in use already).

ps.

Selecting a range that has tons of rows sometimes means that you're padding the
range with empty cells--just in case their filled in.

If that's the case, you could create a name that grew and contracted based on
the data.

Debra Dalgleish shares instructions for that kind of dynamic name:http://contextures.com/xlNames01.html#Dynamic





Mike wrote:

On Nov 12, 11:09 am, "Rick Rothstein"
wrote:
Say I have a block of raws and columns that want to name in a
worksheet using "Box Name".

However, the number of raws is large, or so large, in terms of 100Ks
raws; I don't know what is the max # of raws in Excel new versions.

Yet, I want to use "Box Name" so I use the block name in my work
later. What is the best/smart way to handle this?

If the range is a single area (like A1:X9999), then the number of rows
won't
matter.

If you're trying to use Insert|name|Define (or its equivalent in
xl2007), then
there's a limit on the number of characters in the address (256 I
_think_ in
xl2003).

But if you're trying to name a bunch of non-contiguous rows (and over
100k
rows/cells), then using a name may not be the best technique.

You may want to explain what you're doing a little more.

The block is just few columns, about 6 columns, but the number of raws
might range from 10Ks to 100Ks sometimes!? Yes, it is a continuous
single block or single area. So if I have 150K raws, then the block
will be 6 columns by 150K raws which I want to name using Box Name as
"COMBOS"

Usually, I would highlight the block and, in the Box Name, I would
type COMBOS and hit return. Does it mean I should highligh the 6
columns and scrol-down all the way to the last raw allowed, which is
by the way 65536, and then name the block? If so, does it mean if it
turned that I have 100K combos, i.e. 100K raws, it will still be
there?

Just type your address range in the Name Box first in order to select the
range, then type the name you want to give it in the Name Box to name it.
For example, using your initial posts range of A1:X9999.... just type
A1:X9999 in the Name Box and hit the enter key... that range will then be
selected. Now you can Name the range just as if you had manually selected
the cell yourself.

--
Rick (MVP - Excel)- Hide quoted text -

- Show quoted text -

Could you please clarify how this will be written in the Name Box? Is
it like this "a2:x9999=COMBOS" for example? Walk through
please....Thanks

--

Dave Peterson- Hide quoted text -

- Show quoted text -

The number of COMBOS is not known in advance to pre-define. I tried
a2:f9999 as said above and then named it "COMBOS, it worked!

BUT, f9999 is NOT enough! When I tried a2:f99999 ( i.e. 5 9s) it
didn't take it! Well, I want to name a range of a2:f999999 (yes 6
9s).....How this is done? Any smart way?

I hope there is a way?

Mike

BUT, f9999 is NOT enough! When I tried a2:f99999 ( i.e. 5 9s) it
didn't take it! Well, I want to name a range of a2:f999999 (yes 6
9s).....How this is done? Any smart way?

Go back to Dave's post and read his "PS" section and then go to the link
that he posted in it.

--
Rick (MVP - Excel)

On Nov 12, 1:00*pm, "Rick Rothstein"
wrote:
BUT, f9999 is NOT enough! When I tried a2:f99999 ( i.e. 5 9s) it
didn't take it! Well, I want to name a range of a2:f999999 (yes 6
9s).....How this is done? Any smart way?

Go back to Dave's post and read his "PS" section and then go to the link
that he posted in it.

--
Rick (MVP - Excel)

Thanks Rick....I didn't see it.

First, if you've tried the dynamic range stuff and are happy with that, then
this is just background...

But I'm confused.

You say you have 150k rows. But then you write that the last row is 65536.

So if you really, really wanted and you're using xl97-xl2003, you're limited to
that 64k rows. You'd type:

A1:X65536

If you're using xl2007 and your last row is still 65536, then you're running in
compatibility mode. Save your file as a normal workbook, close it and reopen
that new copy.

Now it should be a "normal" xl2007 workbook with 1 million rows per worksheet.

And you could type:
A1:x99999 (or even larger) in the name box.


Mike wrote:

On Nov 12, 12:04 pm, Dave Peterson wrote:
Go to the name box:
type: A1:X9999
and hit enter.

This selected that large range.

Now while that range is still selected, go back to the name box and type:
COMBOS
and hit enter.

This gives that selected range a name (if that name is not in use already).

ps.

Selecting a range that has tons of rows sometimes means that you're padding the
range with empty cells--just in case their filled in.

If that's the case, you could create a name that grew and contracted based on
the data.

Debra Dalgleish shares instructions for that kind of dynamic name:http://contextures.com/xlNames01.html#Dynamic





Mike wrote:

On Nov 12, 11:09 am, "Rick Rothstein"
wrote:
Say I have a block of raws and columns that want to name in a
worksheet using "Box Name".

However, the number of raws is large, or so large, in terms of 100Ks
raws; I don't know what is the max # of raws in Excel new versions.

Yet, I want to use "Box Name" so I use the block name in my work
later. What is the best/smart way to handle this?

If the range is a single area (like A1:X9999), then the number of rows
won't
matter.

If you're trying to use Insert|name|Define (or its equivalent in
xl2007), then
there's a limit on the number of characters in the address (256 I
_think_ in
xl2003).

But if you're trying to name a bunch of non-contiguous rows (and over
100k
rows/cells), then using a name may not be the best technique.

You may want to explain what you're doing a little more.

The block is just few columns, about 6 columns, but the number of raws
might range from 10Ks to 100Ks sometimes!? Yes, it is a continuous
single block or single area. So if I have 150K raws, then the block
will be 6 columns by 150K raws which I want to name using Box Name as
"COMBOS"

Usually, I would highlight the block and, in the Box Name, I would
type COMBOS and hit return. Does it mean I should highligh the 6
columns and scrol-down all the way to the last raw allowed, which is
by the way 65536, and then name the block? If so, does it mean if it
turned that I have 100K combos, i.e. 100K raws, it will still be
there?

Just type your address range in the Name Box first in order to select the
range, then type the name you want to give it in the Name Box to name it.
For example, using your initial posts range of A1:X9999.... just type
A1:X9999 in the Name Box and hit the enter key... that range will then be
selected. Now you can Name the range just as if you had manually selected
the cell yourself.

--
Rick (MVP - Excel)- Hide quoted text -

- Show quoted text -

Could you please clarify how this will be written in the Name Box? Is
it like this "a2:x9999=COMBOS" for example? Walk through
please....Thanks

--

Dave Peterson- Hide quoted text -

- Show quoted text -

The number of COMBOS is not known in advance to pre-define. I tried
a2:f9999 as said above and then named it "COMBOS, it worked!

BUT, f9999 is NOT enough! When I tried a2:f99999 ( i.e. 5 9s) it
didn't take it! Well, I want to name a range of a2:f999999 (yes 6
9s).....How this is done? Any smart way?

I hope there is a way?

Mike

--

Dave Peterson