title: Excel VBA non contiguous range more than 255 characters error
body: <p>I have code which worked fine. But suddenly when I changed range of cells for copying to another sheet I'm getting the error message &quot;Excel VBA Runtime error '1004' - Method 'Range' of object '_Worksheet' failed&quot;. I guess it's related to character limitation. So I tried to split the range to several zones and use &quot;Union&quot; function which then messed order of copied cells.
  I would really appreciate any help or suggestion.</p>
  <pre><code>Option Explicit
  </code></pre>
  <p>Public Sub SaveExpenses()
  Dim UniqueID(1 To 2)    As Variant, arr() As Variant
  Dim Response            As VbMsgBoxResult
  Dim txtPrompt           As String, FirstAddress As String
  Dim RecordRow           As Long, i As Long
  Dim DataRange           As Range, FoundCell As Range, Cell As Range, Zone1 As Range, Zone2 As Range, Zone3 As Range, Zone4 As Range
  Dim wsDataStorage       As Worksheet, wsExpenses As Worksheet</p>
  <pre><code>With ThisWorkbook
      Set wsDataStorage = .Worksheets(&quot;Data Storage&quot;)
      Set wsExpenses = .Worksheets(&quot;Expenses&quot;)
  End With
  
  Set Zone1 = wsExpenses.Range(&quot;B3,D3,B8:F8,H8:J8,B9:F9,H9:J9,B10:F10,H10:J10,B11:F11,H11:J11,B12:F12,H12:J12,B13:F13,H13:J13&quot;)
  Set Zone2 = wsExpenses.Range(&quot;B17,C17,E17,H17:J17,B18,C18,E18,H18:J18,B19,C19,E19,H19:J19&quot;)
  Set Zone3 = wsExpenses.Range(&quot;B20,C20,E20,H20:J20,B21,C21,E21,H21:J21,B22,C22,E22,H22:J22&quot;)
  Set Zone4 = wsExpenses.Range(&quot;B23,C23,E23,H23:J23,B24,C24,E24,H24:J24,B25,C25,E25,H25:J25,I14,C27,C34&quot;)
  Set DataRange = Union(Zone1, Zone2, Zone3, Zone4)
   
   
  </code></pre>
  <p>'        Set DataRange = wsExpenses.Range(&quot;B3,D3,&quot; &amp; _
  &quot;B8:F8,H8:J8,B9:F9,H9:J9,B10:F10,H10:J10,B11:F11,H11:J11,B12:F12,H12:J12,B13:F13,H13:J13,&quot; &amp; _
  &quot;B17,C17,E17,H17:J17,B18,C18,E18,H18:J18,B19,C19,E19,H19:J19,&quot; &amp; _
  &quot;B20,C20,E20,H20:J20,B21,C21,E21,H21:J21,B22,C22,E22,H22:J22,&quot; &amp; _
  &quot;B23,C23,E23,H23:J23,B24,C24,E24,H24:J24,B25,C25,E25,H25:J25,&quot; &amp; _
  &quot;I14,C27,C34&quot;)</p>
  <pre><code>    'check ID values entered
      For i = 1 To 2
          UniqueID(i) = DataRange.Areas(i)
          If Len(UniqueID(i)) = 0 Then Exit Sub
      Next
   
      'new record
      RecordRow = wsDataStorage.Cells(wsDataStorage.Rows.Count, &quot;B&quot;).End(xlUp).Row + 1
      txtPrompt = &quot;Saved&quot;
   
      'check record exists
      Set FoundCell = wsDataStorage.Columns(2).Find(UniqueID(1), LookIn:=xlValues, LookAt:=xlWhole)
      If Not FoundCell Is Nothing Then
          FirstAddress = FoundCell.Address
          Do
              If UCase(FoundCell.Offset(, 1).Value) = UCase(UniqueID(2)) Then
                  'inform user
                  Response = MsgBox(UniqueID(1) &amp; &quot; &quot; &amp; UniqueID(2) &amp; Chr(10) &amp; _
                  &quot;Record Already Exists&quot; &amp; Chr(10) &amp; _
                  &quot;Do You Want To OverWrite?&quot;, 36, &quot;Record Exists&quot;)
                  If Response = vbNo Then Exit Sub
                  'overwrite record
                  RecordRow = FoundCell.Row
                  txtPrompt = &quot;Updated&quot;
                  Exit Do
              End If
              Set FoundCell = wsDataStorage.Columns(2).FindNext(FoundCell)
              If FoundCell Is Nothing Then Exit Do
          Loop Until FoundCell.Address = FirstAddress
      End If
   
      'size array
      ReDim arr(1 To DataRange.Cells.Count)
      i = 0
      For Each Cell In DataRange.Cells
          i = i + 1
          'non-contiguous form cell values to array
          arr(i) = Cell.Value
      Next Cell
   
      'post arr to range
      wsDataStorage.Range(&quot;B&quot; &amp; RecordRow).Resize(, UBound(arr)).Value = arr
   
      'inform user
      MsgBox &quot;Form no. &quot; &amp; UniqueID(1) &amp; &quot; &quot; &amp; UniqueID(2) &amp; &quot; Successfully &quot; &amp; txtPrompt, 64, &quot;Record &quot; &amp; txtPrompt
   
  
  End Sub
  </code></pre>