Excel - selecting multiple ranges with Range()

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

  • Votes
  • Date
  • Descending date
  1. Hello,

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

    Could you please give us the exact code?

    Best regards.
    1. 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)
    2. 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?
    3. 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 &)
  2. 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 :)