Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
What is the rule for selecting ranges of non-adjacent sections?
What is going on with my selection range below? If I try to add just one more range of cells after 'J104' in the range below, then Excel97 quits. I have been unable to find what is wrong here? I can add just one more individual cell after 'J104", but not a range, not even a range of even 2 cells. Range("C14:C25,C27:C34,C36:C38,C40:C48,C52:C59,C61 :C68,C70:C72,C74:C77,C7 9:C80,C82:C86,C88:C91,C93:C95,C95,C93:C95,C97:C100 ,C104:C118,C121:C125,C1 27:C136,C138:C144,C146:C147," _ & "C150:C152,C155,C157,C159:C162,C164:C173,C175:C185 ,C189:C195,J14:J16,J18: J25,J27:J48,J51:J58,J59:J67,J69,J71:J99,J103:J104" ).Select -Dennis |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
The length of the string for the addresses is too long.
How about dim r1 as range dim r2 as range dim r3 as range 'as many as you need dim rAll as range set r1 = range("c14:c25,c27:c34....") 'lots of addresses set r2 = range("more addresses") set r3 = range("even more addresses") 'as many as you need set rAll = union(r1,r2,r3,...) rAll.select dk_ wrote: What is the rule for selecting ranges of non-adjacent sections? What is going on with my selection range below? If I try to add just one more range of cells after 'J104' in the range below, then Excel97 quits. I have been unable to find what is wrong here? I can add just one more individual cell after 'J104", but not a range, not even a range of even 2 cells. Range("C14:C25,C27:C34,C36:C38,C40:C48,C52:C59,C61 :C68,C70:C72,C74:C77,C7 9:C80,C82:C86,C88:C91,C93:C95,C95,C93:C95,C97:C100 ,C104:C118,C121:C125,C1 27:C136,C138:C144,C146:C147," _ & "C150:C152,C155,C157,C159:C162,C164:C173,C175:C185 ,C189:C195,J14:J16,J18: J25,J27:J48,J51:J58,J59:J67,J69,J71:J99,J103:J104" ).Select -Dennis -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
Dave,
Thank you once again for a lesson. I figured that there were too many parts in my range, but what is the Excel rule for how many parts may be selected? I haven't been able to find that info in the help program. Same question for 'naming' ranges? Any other rules that I should be aware of for selecting or defining ranges? Thanks again. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , Dave Peterson wrote: The length of the string for the addresses is too long. How about dim r1 as range dim r2 as range dim r3 as range 'as many as you need dim rAll as range set r1 = range("c14:c25,c27:c34....") 'lots of addresses set r2 = range("more addresses") set r3 = range("even more addresses") 'as many as you need set rAll = union(r1,r2,r3,...) rAll.select dk_ wrote: What is the rule for selecting ranges of non-adjacent sections? What is going on with my selection range below? If I try to add just one more range of cells after 'J104' in the range below, then Excel97 quits. I have been unable to find what is wrong here? I can add just one more individual cell after 'J104", but not a range, not even a range of even 2 cells. Range("C14:C25,C27:C34,C36:C38,C40:C48,C52:C59,C61 :C68,C70:C72,C74:C77,C7 9:C80,C82:C86,C88:C91,C93:C95,C95,C93:C95,C97:C100 ,C104:C118,C121:C125,C1 27:C136,C138:C144,C146:C147," _ & "C150:C152,C155,C157,C159:C162,C164:C173,C175:C185 ,C189:C195,J14:J16,J18: J25,J27:J48,J51:J58,J59:J67,J69,J71:J99,J103:J104" ).Select -Dennis |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
It's not really too many parts in the range (well, yet). It's too many parts in
that string that you use to define the range. I _think_ that the limit is 256 characters (or about that). I usually keep adding until it breaks, then swear a little bit and go back to fix it. And the same technique can be used for naming a range. rAll.name = "someName" The first thing I'd suggest is stop selecting the range--for most purposes, you can work directly on that range without any select's. dk_ wrote: Dave, Thank you once again for a lesson. I figured that there were too many parts in my range, but what is the Excel rule for how many parts may be selected? I haven't been able to find that info in the help program. Same question for 'naming' ranges? Any other rules that I should be aware of for selecting or defining ranges? Thanks again. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , Dave Peterson wrote: The length of the string for the addresses is too long. How about dim r1 as range dim r2 as range dim r3 as range 'as many as you need dim rAll as range set r1 = range("c14:c25,c27:c34....") 'lots of addresses set r2 = range("more addresses") set r3 = range("even more addresses") 'as many as you need set rAll = union(r1,r2,r3,...) rAll.select dk_ wrote: What is the rule for selecting ranges of non-adjacent sections? What is going on with my selection range below? If I try to add just one more range of cells after 'J104' in the range below, then Excel97 quits. I have been unable to find what is wrong here? I can add just one more individual cell after 'J104", but not a range, not even a range of even 2 cells. Range("C14:C25,C27:C34,C36:C38,C40:C48,C52:C59,C61 :C68,C70:C72,C74:C77,C7 9:C80,C82:C86,C88:C91,C93:C95,C95,C93:C95,C97:C100 ,C104:C118,C121:C125,C1 27:C136,C138:C144,C146:C147," _ & "C150:C152,C155,C157,C159:C162,C164:C173,C175:C185 ,C189:C195,J14:J16,J18: J25,J27:J48,J51:J58,J59:J67,J69,J71:J99,J103:J104" ).Select -Dennis -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
Ahhh, ...256 character limit. So, that's the deal. ;)
Regarding not selecting, ...I just used select to test and see the range. I do try to avoid using ',select' when not needed. I learned that in this group. Now, you partially anwered another question that I was saving, and that was about the Naming. If I use your example ' rAll.name = "someName" ', does that name appear in the Define Name box and the drop down Name Box Menu? If not, how do I get the name to be there? -Dennis In article , Dave Peterson wrote: It's not really too many parts in the range (well, yet). It's too many parts in that string that you use to define the range. I _think_ that the limit is 256 characters (or about that). I usually keep adding until it breaks, then swear a little bit and go back to fix it. And the same technique can be used for naming a range. rAll.name = "someName" The first thing I'd suggest is stop selecting the range--for most purposes, you can work directly on that range without any select's. dk_ wrote: Dave, Thank you once again for a lesson. I figured that there were too many parts in my range, but what is the Excel rule for how many parts may be selected? I haven't been able to find that info in the help program. Same question for 'naming' ranges? Any other rules that I should be aware of for selecting or defining ranges? Thanks again. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , Dave Peterson wrote: The length of the string for the addresses is too long. How about dim r1 as range dim r2 as range dim r3 as range 'as many as you need dim rAll as range set r1 = range("c14:c25,c27:c34....") 'lots of addresses set r2 = range("more addresses") set r3 = range("even more addresses") 'as many as you need set rAll = union(r1,r2,r3,...) rAll.select dk_ wrote: What is the rule for selecting ranges of non-adjacent sections? What is going on with my selection range below? If I try to add just one more range of cells after 'J104' in the range below, then Excel97 quits. I have been unable to find what is wrong here? I can add just one more individual cell after 'J104", but not a range, not even a range of even 2 cells. Range("C14:C25,C27:C34,C36:C38,C40:C48,C52:C59,C61 :C68,C70:C72,C74:C77,C 7 9:C80,C82:C86,C88:C91,C93:C95,C95,C93:C95,C97:C100 ,C104:C118,C121:C125,C 1 27:C136,C138:C144,C146:C147," _ & "C150:C152,C155,C157,C159:C162,C164:C173,C175:C185 ,C189:C195,J14:J16,J18 : J25,J27:J48,J51:J58,J59:J67,J69,J71:J99,J103:J104" ).Select -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
Did it show up in the namebox dropdown when you tried it?
Somethings can be tested quicker than asking and waiting for an answer <bg. dk_ wrote: Ahhh, ...256 character limit. So, that's the deal. ;) Regarding not selecting, ...I just used select to test and see the range. I do try to avoid using ',select' when not needed. I learned that in this group. Now, you partially anwered another question that I was saving, and that was about the Naming. If I use your example ' rAll.name = "someName" ', does that name appear in the Define Name box and the drop down Name Box Menu? If not, how do I get the name to be there? -Dennis In article , Dave Peterson wrote: It's not really too many parts in the range (well, yet). It's too many parts in that string that you use to define the range. I _think_ that the limit is 256 characters (or about that). I usually keep adding until it breaks, then swear a little bit and go back to fix it. And the same technique can be used for naming a range. rAll.name = "someName" The first thing I'd suggest is stop selecting the range--for most purposes, you can work directly on that range without any select's. dk_ wrote: Dave, Thank you once again for a lesson. I figured that there were too many parts in my range, but what is the Excel rule for how many parts may be selected? I haven't been able to find that info in the help program. Same question for 'naming' ranges? Any other rules that I should be aware of for selecting or defining ranges? Thanks again. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , Dave Peterson wrote: The length of the string for the addresses is too long. How about dim r1 as range dim r2 as range dim r3 as range 'as many as you need dim rAll as range set r1 = range("c14:c25,c27:c34....") 'lots of addresses set r2 = range("more addresses") set r3 = range("even more addresses") 'as many as you need set rAll = union(r1,r2,r3,...) rAll.select dk_ wrote: What is the rule for selecting ranges of non-adjacent sections? What is going on with my selection range below? If I try to add just one more range of cells after 'J104' in the range below, then Excel97 quits. I have been unable to find what is wrong here? I can add just one more individual cell after 'J104", but not a range, not even a range of even 2 cells. Range("C14:C25,C27:C34,C36:C38,C40:C48,C52:C59,C61 :C68,C70:C72,C74:C77,C 7 9:C80,C82:C86,C88:C91,C93:C95,C95,C93:C95,C97:C100 ,C104:C118,C121:C125,C 1 27:C136,C138:C144,C146:C147," _ & "C150:C152,C155,C157,C159:C162,C164:C173,C175:C185 ,C189:C195,J14:J16,J18 : J25,J27:J48,J51:J58,J59:J67,J69,J71:J99,J103:J104" ).Select -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
I didn't test it, and I realized that I should have, before posting
another question. -Dennis In article , Dave Peterson wrote: Did it show up in the namebox dropdown when you tried it? Somethings can be tested quicker than asking and waiting for an answer <bg. dk_ wrote: Ahhh, ...256 character limit. So, that's the deal. ;) Regarding not selecting, ...I just used select to test and see the range. I do try to avoid using ',select' when not needed. I learned that in this group. Now, you partially anwered another question that I was saving, and that was about the Naming. If I use your example ' rAll.name = "someName" ', does that name appear in the Define Name box and the drop down Name Box Menu? If not, how do I get the name to be there? -Dennis In article , Dave Peterson wrote: It's not really too many parts in the range (well, yet). It's too many parts in that string that you use to define the range. I _think_ that the limit is 256 characters (or about that). I usually keep adding until it breaks, then swear a little bit and go back to fix it. And the same technique can be used for naming a range. rAll.name = "someName" The first thing I'd suggest is stop selecting the range--for most purposes, you can work directly on that range without any select's. dk_ wrote: Dave, Thank you once again for a lesson. I figured that there were too many parts in my range, but what is the Excel rule for how many parts may be selected? I haven't been able to find that info in the help program. Same question for 'naming' ranges? Any other rules that I should be aware of for selecting or defining ranges? Thanks again. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , Dave Peterson wrote: The length of the string for the addresses is too long. How about dim r1 as range dim r2 as range dim r3 as range 'as many as you need dim rAll as range set r1 = range("c14:c25,c27:c34....") 'lots of addresses set r2 = range("more addresses") set r3 = range("even more addresses") 'as many as you need set rAll = union(r1,r2,r3,...) rAll.select dk_ wrote: What is the rule for selecting ranges of non-adjacent sections? What is going on with my selection range below? If I try to add just one more range of cells after 'J104' in the range below, then Excel97 quits. I have been unable to find what is wrong here? I can add just one more individual cell after 'J104", but not a range, not even a range of even 2 cells. Range("C14:C25,C27:C34,C36:C38,C40:C48,C52:C59,C61 :C68,C70:C72,C74:C 77,C 7 9:C80,C82:C86,C88:C91,C93:C95,C95,C93:C95,C97:C100 ,C104:C118,C121:C1 25,C 1 27:C136,C138:C144,C146:C147," _ & "C150:C152,C155,C157,C159:C162,C164:C173,C175:C185 ,C189:C195,J14:J16 ,J18 : J25,J27:J48,J51:J58,J59:J67,J69,J71:J99,J103:J104" ).Select -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture -- Dennis Kessler http://www.denniskessler.com/acupuncture |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
Regarding the guess at a 256 character limit, that's not it. I just
checked. The range that works is 298 characters, and with code surrounding the range it's 298 characters, and I could add one more cell, for another 4 characters (and that was the limit). There must be another rule operating that limits how a range can be described. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , Dave Peterson wrote: I _think_ that the limit is 256 characters (or about that). I usually keep adding until it breaks, then swear a little bit and go back to fix it. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
298 or 256 -- what's the difference between friends? <vbg.
dk_ wrote: Regarding the guess at a 256 character limit, that's not it. I just checked. The range that works is 298 characters, and with code surrounding the range it's 298 characters, and I could add one more cell, for another 4 characters (and that was the limit). There must be another rule operating that limits how a range can be described. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , Dave Peterson wrote: I _think_ that the limit is 256 characters (or about that). I usually keep adding until it breaks, then swear a little bit and go back to fix it. -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
Come' on, someone must know the rule. ;)
Excel is not as generous as living with a rule that says, "298 or 256 whatever." -Dennis In article , Dave Peterson wrote: 298 or 256 -- what's the difference between friends? <vbg. dk_ wrote: Regarding the guess at a 256 character limit, that's not it. I just checked. The range that works is 298 characters, and with code surrounding the range it's 298 characters, and I could add one more cell, for another 4 characters (and that was the limit). There must be another rule operating that limits how a range can be described. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , Dave Peterson wrote: I _think_ that the limit is 256 characters (or about that). I usually keep adding until it breaks, then swear a little bit and go back to fix it. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
How about you doing some testing and then post back with your results.
Personally, I'll never remember what that number is (as I've shown!). I just remember that long strings cause trouble and know that I have to work around them. Do you actually think that you'll remember this exact number in a couple of months? <vbg dk_ wrote: Come' on, someone must know the rule. ;) Excel is not as generous as living with a rule that says, "298 or 256 whatever." -Dennis In article , Dave Peterson wrote: 298 or 256 -- what's the difference between friends? <vbg. dk_ wrote: Regarding the guess at a 256 character limit, that's not it. I just checked. The range that works is 298 characters, and with code surrounding the range it's 298 characters, and I could add one more cell, for another 4 characters (and that was the limit). There must be another rule operating that limits how a range can be described. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , Dave Peterson wrote: I _think_ that the limit is 256 characters (or about that). I usually keep adding until it breaks, then swear a little bit and go back to fix it. -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range selection rule?
Dave,
I agree that the number is not really not important. I was thinking that there is some other rule operating, and that someone else might chime in here with the real answer. Thanks for hanging in there. :) -Dennis In article , Dave Peterson wrote: How about you doing some testing and then post back with your results. Personally, I'll never remember what that number is (as I've shown!). I just remember that long strings cause trouble and know that I have to work around them. Do you actually think that you'll remember this exact number in a couple of months? <vbg dk_ wrote: Come' on, someone must know the rule. ;) Excel is not as generous as living with a rule that says, "298 or 256 whatever." -Dennis In article , Dave Peterson wrote: 298 or 256 -- what's the difference between friends? <vbg. dk_ wrote: Regarding the guess at a 256 character limit, that's not it. I just checked. The range that works is 298 characters, and with code surrounding the range it's 298 characters, and I could add one more cell, for another 4 characters (and that was the limit). There must be another rule operating that limits how a range can be described. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , Dave Peterson wrote: I _think_ that the limit is 256 characters (or about that). I usually keep adding until it breaks, then swear a little bit and go back to fix it. -- Dennis Kessler http://www.denniskessler.com/acupuncture |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Ranges using non-contiguous cells and dependent on a cell value | Excel Worksheet Functions | |||
First row in Selection range (first index of a cell) EXCEL VBA | Excel Worksheet Functions | |||
'Centre across selection' format to apply to vertical range | Excel Discussion (Misc queries) | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
random selection from a range of cells | Excel Worksheet Functions |