Hi,

Try this, right click your sheet tab, view code and paste this in

Sub stance()
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("B1:B" & lastrow)
For Each c In myrange
If Not Application.WorksheetFunction.IsNA(c.Value) Then
c.Offset(0, -1).Value = c.Value
End If
Next
End Sub


Mike

" wrote:

I want to merge the two columns, sample data below:


CNM1GBLO #N/A
CNM1GBLO #N/A
CNP1GBLO #N/A
CNP1GBLO NIMWGBLO
CNQ1GBLO #N/A
CNR1GBLO #N/A
CNR1GBLO #N/A


What I want is for any value in the righthand column which is not
equal to #N/A to overwrite the value in the lefthand column on the
corresponding row.

So in this case, after the merge, column A would remain the same,
except that CNP1GBLO would now overwritten with NIMWGBLO.