title: How to copy a union of discontinuous ranges and paste them into another sheet?
body: <p>I've got a form in excel looks like this:</p>
  
  
  
  <pre><code>  E F G H ... N O P Q
    * *   *     * *   *
    * *   *     * *   *
                * *   *
                * *   *
                * *   *
  T:* *   *   T:* *   *
  
    * *   *     * *   *
    * *   *     * *   *
    * *   *
    * *   *
  
  T:* *   *   T:* *   *
  
    * *   *
    * *   *
  
  
  
  T:* *   *
  </code></pre>
  
  <p>It consists of many small areas with subtotals - rows indicated with "T". </p>
  
  <p>Column E is "Price" and "F" is Qty, the rest of them is either formula calculated, or empty.
  So I've written a function to collect data from "E", which is initially what I had wanted. </p>
  
  <p>But now I also wanted to get data from "F" and "H" as well, when "E" is validated.</p>
  
  <p>My code was: </p>
  
  <pre class="lang-vb prettyprint-override"><code>Private Function CollectCellsData(dataRange As Range) As Range
  Dim cell As Range, newRange As Range
  
  For Each cell In dataRange
  
      If Not cell.HasFormula = True And Not IsEmpty(cell.Value) Then
          If newRange Is Nothing Then
              Set newRange = cell
          Else
              Set newRange = Union(newRange, cell)
          End If
      End If
  Next
  Set CollectCellsData = newRange
  
  End Function
  
  Private Function CopyDataAndPaste(sSheet As Worksheet, sColumn As String, dSheet As Worksheet, dColumn As String)
  Dim lastRow As Long
  Dim dataRange As Range, newRange As Range
  
  lastRow = sSheet.Cells(Rows.Count, sColumn).End(xlUp).Row
  Set dataRange = sSheet.Range(sColumn &amp; "3:" &amp; sColumn &amp; lastRow)
  Set newRange = CollectCellsData(dataRange)
  
  lastRow = dSheet.Cells(Rows.Count, dColumn).End(xlUp).Row
  If Not newRange Is Nothing Then
      newRange.Copy
      dSheet.Range(dColumn &amp; lastRow + 1).PasteSpecial Paste:=xlPasteValues
      Application.CutCopyMode = False
  End If
  
  End Function
  </code></pre>
  
  <p>And I thought the most simple way to do it was simply alternate:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Set newRange = Union(newRange, cell)
  </code></pre>
  
  <p>into:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Set newRange = Union(newRange, cell, cell.Offset(0,1), cell.Offset(0,3))
  </code></pre>
  
  <p>But apparently I was wrong. The error message is</p>
  
  <pre class="lang-vb prettyprint-override"><code>"Error 1004: Command cannot be used on multiple selection"
  </code></pre>
  
  <p>I think I've made a conceptional mistake. But if a</p>
  
  <pre class="lang-vb prettyprint-override"><code>Union(range1, range2, range3)
  </code></pre>
  
  <p>would work with .Copy, why not in my case?</p>
  
  <p>EDIT:</p>
  
  <p>My bad, after I change the code into</p>
  
  <pre class="lang-vb prettyprint-override"><code>Set newRange = Union(newRange, cell, cell.Offset(0,1), cell.Offset(0,3))
  </code></pre>
  
  <p>There's an error occurred at line</p>
  
  <pre class="lang-vb prettyprint-override"><code>newRange.Copy
  </code></pre>
  
  <p>After the emphasis of <em>Chrismas007</em> that the Union() method should work, and a hint of msgbox rng.address for debugging, I'm now able to make it work. The problem was with the assignment of "newRange", not the second one but the initial assignment. Like what <em>Gary's Student</em> has implied, Union collects cells in a uniform way.</p>
  
  <pre class="lang-vb prettyprint-override"><code>'error
  Set newRange = cell
  
  'run
  Set newRange = Union(cell, cell.Offset(0, 1), cell.Offset(0, 3))
  </code></pre>
  
  <p>Dropped programming for years and now I'm like the newbie 10 years ago!</p>