excel - Pasting a range of Data into a new column - omitting empty cells, but keeping same row -

i have data in 2 columns (a , b) has been generated through excel formulas within each cell. not cells have visible value, have embedded formula.

i.e.: col a

row 1 = "677"   row 2 = ""     <-- (not empty has formula) row 3 = "345" row 4 = ""     <-- (not empty has formula) 

every have manually paste these values 2 other columns in sheet, (col c , d) supposed replace other data exists in cells... i.e. if there value copy paste in, if there no value leave existing data in col c , d intact.

when tell paste special , "skip blanks", still counts "" (empty) cells not blank because of hidden formula in them, , overwrites existing data in cells in col c , d empty cell values.

i had idea of writing macro select source range, , loop through range a2:b1200 (using "for each cell" in range), copying , pasting cells not empty, preserving old data.

i got point cannibalizing other functions. not see how paste non-empty values cell (offset 2 columns right).

edit:

i shifting approach set range cols c , d , trying pull values selected cell, rather forcing values other column.... update code example later (not yet done).

public sub cleanupspacedlist()  dim tfcol range, cell object  dim lastrow long 'evaluate last row lastrow = activesheet.cells(rows.count, "a").end(xlup).row  set tfcol = range("a2:a" & lastrow)  each cell in tfcol  if isempty(cell)   'nothing else   cell.copy     'paste 2 cells right i.e. copy cell in col cell in col c   'selection.offset(1, 0).select     '-- stuck here   'cell.paste end if  next  end sub 

try following:

cell.copy cell.offset(0, 2) 

or

cell.offset(0,2).value = cell.value  

you not need use select command when defining range did cell. select used macro recorder typically substituted range name when code modified. setting range did opens door properties , methods contained within excel.

to learn more, type cell. list of possible properties , methods can use read or assign properties active cell.