title: Pasting a range of Data into a new column - omitting empty cells, but keeping same row
body: <p>I have data in 2 columns (A and B) which has been generated through Excel formulas within each cell. Not all cells have a visible value, but all have an embedded formula.</p>
  
  <p>i.e.: Col A</p>
  
  <pre><code>Row 1 = "677"  
  Row 2 = ""     &lt;-- (not empty as it has a formula)
  Row 3 = "345"
  Row 4 = ""     &lt;-- (not empty as it has a formula)
  </code></pre>
  
  <p>Every so often I have to manually paste these values into 2 other columns in the sheet, (Col C and D) where it is supposed to replace other data which already exists in some cells... I.E. if there is a value to copy it will paste in, but if there is no value it will leave the existing data in Col C and D intact. </p>
  
  <p>When I tell it to Paste Special and "skip blanks", it still counts the "" (empty) cells as NOT blank because of the hidden formula in them, and then overwrites existing data in some cells in Col C and D with empty cell values.</p>
  
  <p>I had the idea of writing a macro which would select my source range, and loop through the range A2:B1200 (using a "For each cell" in the range), only copying and pasting cells which are not empty, thus preserving my old data.</p>
  
  <p>I got to a certain point by cannibalizing other functions. I do not see how it would paste the non-empty values to another cell (offset by 2 columns to the right).</p>
  
  <p>EDIT:</p>
  
  <p>I am now shifting my approach to set my range as Cols C and D and trying to pull values into the currently selected cell, rather than forcing values into the other column.... I will update my code example later (not yet done).</p>
  
  <pre><code>Public Sub CleanUpSpacedList()
  
  Dim tfCol As Range, Cell As Object
  
  Dim lastRow As Long
  'evaluate last row
  lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
  
  Set tfCol = Range("A2:A" &amp; lastRow)
  
  For Each Cell In tfCol
  
  If IsEmpty(Cell) Then
    'nothing
  Else
    Cell.Copy  
    'paste 2 cells to the right i.e. copy cell in col A to cell in col C
    'Selection.Offset(1, 0).Select  
    '-- stuck here
    'Cell.Paste
  End If
  
  Next
  
  End Sub
  </code></pre>