View original (French)

Excel - selecting multiple ranges with Range()

David -  
 David -
Bonjour,

I need to select multiple areas on a sheet to change the background color.

For this, I created a macro where I select named ranges: 
Range("zone1, zone2, ... zoneX").Select


But if the string "zone1, ...zoneX" exceeds 255 characters, Excel shows a runtime error for the macro: 
error 1004: the 'range' method of the '_global' object has failed


My question: is there a way for Range to handle a string longer than 255 characters in a way that allows for a single selection? Or am I forced to proceed in multiple parts and therefore loop through several selections?

Configuration: Windows 7 / Chrome 45.0.2454.101

2 answers

  1.
    Kuartz Posted messages 852 Registration date   Status Member Last intervention   65
     
    Hello,

    I don't quite understand, a selection in Excel is unlimited.

    Could you please give us the exact code?

    Best regards.
    1
    1. David
       
      The code is quite simple.

      This works (the string cellules is 250 characters long): 
      Dim cellules as String
      cellules = "zone_1, zone_2, zone_3, zone_4, zone_5, zone_6, zone_7, zone_8, zone_9, zone_10, zone_11, zone_12, zone_13, zone_14, zone_15, zone_16, zone_17, zone_18, zone_19, zone_20, zone_21, zone_22, zone_23, zone_24, zone_25, zone_26, zone_27, zone_28, zone_29"
      Range(cellules).Select


      This does not work (the string cellules is 259 characters long): 
      Dim cellules as String
      cellules = "zone_1, zone_2, zone_3, zone_4, zone_5, zone_6, zone_7, zone_8, zone_9, zone_10, zone_11, zone_12, zone_13, zone_14, zone_15, zone_16, zone_17, zone_18, zone_19, zone_20, zone_21, zone_22, zone_23, zone_24, zone_25, zone_26, zone_27, zone_28, zone_29, zone_30"
      Range(cellules).Select


      I could have changed the names of the zones to z_1, ..., z_30 but it's long
      I worked around the problem by making 2 selections instead of one (thereby launching 2 processes instead of one)
      0
    2. Kuartz Posted messages 852 Registration date   Status Member Last intervention   65
       
      A character string from a String type variable can contain about 2^31 (this is theoretical) characters. So a little over 2 billion. I doubt that's where the problem lies. There must be another issue in your code.

      Where did you define the zones?
      0
    3. David
       
      The zones are defined in a classic sheet, by hand, and there are no naming errors (I've checked several times).

      Defining a string longer than 255 characters is not a problem (see the second example). The issue is solely with using range(cells) with cells > 255 characters, whether by passing through a string or directly (range("zone1 ... zone30")).

      (PS: I also tried cutting the string by concatenating with &)
      0
    4. PlacageGranby Posted messages 402 Status Member 26
       
      Hello,

      I did a little research on Google to see if we can merge areas.
      We don't find anything on this, but then I searched if we could merge range objects. And here we are
      https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa213609(v=office.11)?redirectedfrom=MSDN

      I found the Union method.


      So 
       Dim Cellules as range Set Cellules = Application.Union(Range("zone_1"), Range("zone_2"),Range("zone_3")) Cellules.select
      0
      1. David > PlacageGranby Posted messages 402 Status Member
         
        Thank you, that confirms my research.
        0
  2. David
     
    Hello,

    I think I found it: it seems there is indeed a limit of 255 characters for strings used as method parameters (but not for subs ...): for examples, search for "excel vba 255 character limit parameter"

    The workaround is to use Union(Range("zone1")[, Range("zone2"...]) to construct a Range as in my case.

    Thanks to Kuartz :)
    1