title: How to change part of selected range in a selection of multiple non-contiguous range
body: <p>I will try to illustrate the scenario by only focus on the problem and take out all the non essential issues/scripts.</p>
  
  <p>User selects multiple non-contiguous cells. VBA would do a few things...then insert a column.
  If the selected cells happened to be at the right side of the columns, the content would move one column right of the original selected cells. I need to re-select the cells with the original content before exit sub.</p>
  
  <p>For instance, </p>
  
  <ol>
  <li>the user selects "A1", "C3", and "D4:E6". </li>
  <li>the vba insert a column at "B"</li>
  <li>now the content of "C3" and "D4:E6" move to "D3" and "E4:F6" </li>
  <li>I need the vba to select "A1", "D3" and "E4:F6" before exit sub.</li>
  </ol>
  
  <p>I have considered a few methods:</p>
  
  <ol>
  <li><p>Offset the entire selection to one column right.</p>
  
  <p><code>selection.offset(0,1).select</code></p>
  
  <p>This is not a good solution as "A1" would move to "B1". It is only ok if the user selected cells are all at the right side of the inserted column.</p></li>
  <li><p>Put each cell of selection (selected range) into array. Change the affected cells' range. and use vba to select them all again. The problem is that the vba I wrote can't select the entire array of ranges (multiple non contiguous cells) at once. It only selects the last cell in the array. Here is the summarized code:</p>
  
  <pre><code>Sub mtArea()
  
  Dim Cell, Rg, sRg() As Range
  Dim h, i, j, k, noCell, Cnt As Long
  Set Rg = Selection
  
  noCell = Rg.Cells.Count
  k = 0
  
  ' assign each cell in selection to a specific array
  
  If Rg.Areas.Count &gt; 1 Then
     ReDim sRg(noCell)
     For Each Cell In Rg
        k = k + 1
        Set sRg(k) = Cell
     Next Cell
  End If
  
  ' select the new located cells
  
  For i = 1 To noCell
   If sRg(i).Column &gt; 5 Then ' assuming insert column is "E"
     h = 1
   Else
     h = 0
   End If
  sRg(i).Offset(0, h).Select
  Next i
  
  End Sub
  </code></pre>
  
  <p>In this case, only the last cell in the original range is being selected. Is there a way to select the entire sRg() range array?</p></li>
  <li><p>I also hope to explore this way: </p>
  
  <pre><code>Dim Rg as Range
  Set Rg = Selection
  </code></pre>
  
  <p>When user selects multiple non contiguous cells, is there a way for vba to change the individual cells range location in the Rg variable?</p></li>
  </ol>
  
  <p>What should be the method?</p>
  
  <p>Thank you.</p>