| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula via Define Name (not working) | Excel Discussion (Misc queries) | |||
| Cannot insert row | Excel Worksheet Functions | |||
| Can I auto insert a worksheet when I insert a value in a cell. | Excel Worksheet Functions | |||
| Insert Options button doesn't disappear | Excel Discussion (Misc queries) | |||
| Define Names in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
