Insert-Name-Define limit ?
 

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or row.

The purpose of the name is to group those cells that need to cleared when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

Names may have limitations that string do not have. Try something like:


Sub sistence()
Dim CLEAR_OLD_DATA As String, r As Range
CLEAR_OLD_DATA = "A1:C9,A11:D13"
Range(CLEAR_OLD_DATA).Clear
End Sub

--
Gary's Student


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or row.

The purpose of the name is to group those cells that need to cleared when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

Thanks Gary, but is it not possible instead of listing each cell, rather use
a range name?

"Gary''s Student" wrote:

Names may have limitations that string do not have. Try something like:


Sub sistence()
Dim CLEAR_OLD_DATA As String, r As Range
CLEAR_OLD_DATA = "A1:C9,A11:D13"
Range(CLEAR_OLD_DATA).Clear
End Sub

--
Gary's Student


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or row.

The purpose of the name is to group those cells that need to cleared when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

The "Refers to" property has a limit of 255 characters.

You can get around this limitation by defining the named range this way:
1 - Select all the cells you want to include in the named range
2 - Enter the name of the range in the "Name box" of the formula bar
(search Excel's help for "name range").
This technique works fine in Excel, but may cause surprises if you
manipulate the "Names" collection through VBA (rarely an issue).

Another way, is to create names for part of your range and use those names
to create the larger named range...


--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or row.

The purpose of the name is to group those cells that need to cleared when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

PapaDos,

I have tried to create other names, and then when putting in the main name,
the macro comes back with a run time error.

"PapaDos" wrote:

The "Refers to" property has a limit of 255 characters.

You can get around this limitation by defining the named range this way:
1 - Select all the cells you want to include in the named range
2 - Enter the name of the range in the "Name box" of the formula bar
(search Excel's help for "name range").
This technique works fine in Excel, but may cause surprises if you
manipulate the "Names" collection through VBA (rarely an issue).

Another way, is to create names for part of your range and use those names
to create the larger named range...


--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or row.

The purpose of the name is to group those cells that need to cleared when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

PapaDos, tried the selecting all the cells and it only selected the first 10
ranges.

"PapaDos" wrote:

The "Refers to" property has a limit of 255 characters.

You can get around this limitation by defining the named range this way:
1 - Select all the cells you want to include in the named range
2 - Enter the name of the range in the "Name box" of the formula bar
(search Excel's help for "name range").
This technique works fine in Excel, but may cause surprises if you
manipulate the "Names" collection through VBA (rarely an issue).

Another way, is to create names for part of your range and use those names
to create the larger named range...


--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or row.

The purpose of the name is to group those cells that need to cleared when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

You probably made an error in defining the names.
It should work OK.

Post a simplified example of what you did...

--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

PapaDos,

I have tried to create other names, and then when putting in the main name,
the macro comes back with a run time error.

"PapaDos" wrote:

The "Refers to" property has a limit of 255 characters.

You can get around this limitation by defining the named range this way:
1 - Select all the cells you want to include in the named range
2 - Enter the name of the range in the "Name box" of the formula bar
(search Excel's help for "name range").
This technique works fine in Excel, but may cause surprises if you
manipulate the "Names" collection through VBA (rarely an issue).

Another way, is to create names for part of your range and use those names
to create the larger named range...


--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or row.

The purpose of the name is to group those cells that need to cleared when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

I would guess it is because the Refersto value is too long. When you select
a range, say A1:A10 and try to assign a name to it, Excel automatically adds
the sheet name, Sheet1!A1:A10.

So if you select three ranges, B4:B7, e$, H4 you will get

=Sheet1!$B$4:$B$7,Sheet1!$E$9,Sheet1!$H$4

which means that it runs out of room (255 chars) very quickly in your case.

What you could do is to go into the name definition, Ctrl-F3, select your
name and then go edit the RefersTo box, select it and F2. Remove all the
Sheet1! bits from what is already there, and then you can manuallt type some
more on the end (without the sheet name. Excel will add the sheet name
again, but you have got past the 255 restriction.

What I tend to do is to copy the value in RefersT, paste it into a decent
text editor (not Notepad), and edit it there with toools such as find &
Replace. It is easier to see what is going on. Then just paste it back.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Sunnyskies" wrote in message
...
Thanks Gary, but is it not possible instead of listing each cell, rather
use
a range name?

"Gary''s Student" wrote:

Names may have limitations that string do not have. Try something like:


Sub sistence()
Dim CLEAR_OLD_DATA As String, r As Range
CLEAR_OLD_DATA = "A1:C9,A11:D13"
Range(CLEAR_OLD_DATA).Clear
End Sub

--
Gary's Student


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but
the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or
row.

The purpose of the name is to group those cells that need to cleared
when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

Okay, need some guidenance.

Here are my list of name:
Clear_Old_Data_BVO
Clear_Old_Data_Cleanup
Clear_Old_Data_Dates
Clear_Old_Data_Header
Clear_Old_Data_HR
Clear_Old_Data_Incident

Now the main name is Clear_Old_Data

In the Refers to field what do I type there?

Thanks

"PapaDos" wrote:

You probably made an error in defining the names.
It should work OK.

Post a simplified example of what you did...

--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

PapaDos,

I have tried to create other names, and then when putting in the main name,
the macro comes back with a run time error.

"PapaDos" wrote:

The "Refers to" property has a limit of 255 characters.

You can get around this limitation by defining the named range this way:
1 - Select all the cells you want to include in the named range
2 - Enter the name of the range in the "Name box" of the formula bar
(search Excel's help for "name range").
This technique works fine in Excel, but may cause surprises if you
manipulate the "Names" collection through VBA (rarely an issue).

Another way, is to create names for part of your range and use those names
to create the larger named range...


--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or row.

The purpose of the name is to group those cells that need to cleared when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

Hi

Your problem is the length of the names. They average just over 20
characters in length each, of which the first 14 are repeated.
If you try to add them all together into one range name, you would only
manage 11 or 12 before running into the 255 character limit. I believe
you said you had 31 ranges.

If so, do it in 2 stages.
rng1 = 11 of your ranges, rng2 the next 11 and rng3 the final 9
Then Clear_Old_Data =rng1,rng2,rng3

--
Regards

Roger Govier


"Sunnyskies" wrote in message
...
Okay, need some guidenance.

Here are my list of name:
Clear_Old_Data_BVO
Clear_Old_Data_Cleanup
Clear_Old_Data_Dates
Clear_Old_Data_Header
Clear_Old_Data_HR
Clear_Old_Data_Incident

Now the main name is Clear_Old_Data

In the Refers to field what do I type there?

Thanks

"PapaDos" wrote:

You probably made an error in defining the names.
It should work OK.

Post a simplified example of what you did...

--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

PapaDos,

I have tried to create other names, and then when putting in the
main name,
the macro comes back with a run time error.

"PapaDos" wrote:

The "Refers to" property has a limit of 255 characters.

You can get around this limitation by defining the named range
this way:
1 - Select all the cells you want to include in the named range
2 - Enter the name of the range in the "Name box" of the
formula bar
(search Excel's help for "name range").
This technique works fine in Excel, but may cause surprises if
you
manipulate the "Names" collection through VBA (rarely an issue).

Another way, is to create names for part of your range and use
those names
to create the larger named range...


--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name
(CLEAR_OLD_DATA), but the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific
column or row.

The purpose of the name is to group those cells that need to
cleared when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

See my response earlier

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Sunnyskies" wrote in message
...
PapaDos, tried the selecting all the cells and it only selected the first
10
ranges.

"PapaDos" wrote:

The "Refers to" property has a limit of 255 characters.

You can get around this limitation by defining the named range this way:
1 - Select all the cells you want to include in the named range
2 - Enter the name of the range in the "Name box" of the formula bar
(search Excel's help for "name range").
This technique works fine in Excel, but may cause surprises if you
manipulate the "Names" collection through VBA (rarely an issue).

Another way, is to create names for part of your range and use those
names
to create the larger named range...


--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but
the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or
row.

The purpose of the name is to group those cells that need to cleared
when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

Will try your suggestion.

Thanks

"Bob Phillips" wrote:

See my response earlier

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Sunnyskies" wrote in message
...
PapaDos, tried the selecting all the cells and it only selected the first
10
ranges.

"PapaDos" wrote:

The "Refers to" property has a limit of 255 characters.

You can get around this limitation by defining the named range this way:
1 - Select all the cells you want to include in the named range
2 - Enter the name of the range in the "Name box" of the formula bar
(search Excel's help for "name range").
This technique works fine in Excel, but may cause surprises if you
manipulate the "Names" collection through VBA (rarely an issue).

Another way, is to create names for part of your range and use those
names
to create the larger named range...


--
Regards,
Luc.

"Festina Lente"


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but
the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or
row.

The purpose of the name is to group those cells that need to cleared
when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks

Insert-Name-Define limit ?
 

Afternoon Bob,

I did as you mentioned. Took:
=IncidentForm!$D$8:$D$10,IncidentForm!$G$8:$G$10,I ncidentForm!$J$8
=IncidentForm!$D$19,IncidentForm!$G$19,IncidentFor m!$D$21,IncidentForm!$F$21,IncidentForm!$I$21,Inci dentForm!$M$21
=IncidentForm!$L$26,IncidentForm!$L$29,IncidentFor m!$L$31,IncidentForm!$L$33
=IncidentForm!$D$5:$D$6,IncidentForm!$G$5:$G$6,Inc identForm!$J$4:$J$6
=IncidentForm!$D$27,IncidentForm!$F$27,IncidentFor m!$I$27,IncidentForm!$K$27
=IncidentForm!$D$12:$D$14,IncidentForm!$G$12:$G$14 ,IncidentForm!$J$12:$J$14,IncidentForm!$M$12:$M$14 ,IncidentForm!$D$16,IncidentForm!$G$16,IncidentFor m!$I$16,IncidentForm!$K$16,IncidentForm!$D$17,Inci dentForm!$G$17,IncidentForm!$I$17

And removed IncidentForm! to reduce it to this:
=$D$8:$D$10,$G$8:$G$10,$J$8,$D$19,$G$19,$D$21,$F$2 1,$I$21,$M$21,$L$26,$L$29,$L$31,$L$33,$D$5:$D$6,$G $5:$G$6,$J$4:$J$6,$D$27,$F$27,$I$27,$K$27,$D$12:$D $14,$G$12:$G$14,$J$12:$J$14,$M$12:$M$14,$D$16,$G$1 6,$I$16,$K$16,$D$17,$G$17,$I$17

And when I copied from Word back to Excel into the Refers tobox
It put the IncidentForm! infront of each cell thus limiting the Refers to
$L$29 (see below):
=IncidentForm!$D$8:$D$10,IncidentForm!$G$8:$G$10,I ncidentForm!$J$8,IncidentForm!$D$19,IncidentForm!$ G$19,IncidentForm!$D$21,IncidentForm!$F$21,Inciden tForm!$I$21,IncidentForm!$M$21,IncidentForm!$L$26, IncidentForm!$L$29 IncidentForm!

Did I follow your instructions correctly?


"Bob Phillips" wrote:

I would guess it is because the Refersto value is too long. When you select
a range, say A1:A10 and try to assign a name to it, Excel automatically adds
the sheet name, Sheet1!A1:A10.

So if you select three ranges, B4:B7, e$, H4 you will get

=Sheet1!$B$4:$B$7,Sheet1!$E$9,Sheet1!$H$4

which means that it runs out of room (255 chars) very quickly in your case.

What you could do is to go into the name definition, Ctrl-F3, select your
name and then go edit the RefersTo box, select it and F2. Remove all the
Sheet1! bits from what is already there, and then you can manuallt type some
more on the end (without the sheet name. Excel will add the sheet name
again, but you have got past the 255 restriction.

What I tend to do is to copy the value in RefersT, paste it into a decent
text editor (not Notepad), and edit it there with toools such as find &
Replace. It is easier to see what is going on. Then just paste it back.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Sunnyskies" wrote in message
...
Thanks Gary, but is it not possible instead of listing each cell, rather
use
a range name?

"Gary''s Student" wrote:

Names may have limitations that string do not have. Try something like:


Sub sistence()
Dim CLEAR_OLD_DATA As String, r As Range
CLEAR_OLD_DATA = "A1:C9,A11:D13"
Range(CLEAR_OLD_DATA).Clear
End Sub

--
Gary's Student


"Sunnyskies" wrote:

Morning,

I wish to select 31 ranges and give them a name (CLEAR_OLD_DATA), but
the
selection stops at 11 ranges, why?

They are all on one spreadsheet but are not in a specific column or
row.

The purpose of the name is to group those cells that need to cleared
when
the New Report macro is selected thus use the vba:
RANGE("CLEAR_OLD_DATA").CLEARCONTENT

So what can you advise?

Thanks