Copy Non Blank Cells From Range to Range

Question

I wonder if you can help me with this:

Ranges B11:B251 & C11:C251 may or may not have some values. I want to be able to copy non blank cells from cell ranges M11:M251 & N11:N251 to B11:B251 & C11:C251, so if there are any values in M&N ranges they should overwrite values in the same rows in B&C but if there are blank values in M&N ranges they should not be copied and leave the values already present (or not) in B&C. Was I clear? ;-)

Thanks for any replies!

Answer 1

Sub Main()
    Dim i As Long
    For i = 11 To 251
        If Not IsEmpty(Range("M" & i)) Then _
            Range("B" & i) = Range("M" & i)
        If Not IsEmpty(Range("N" & i)) Then _
            Range("C" & i) = Range("N" & i)
    Next i
End Sub

this code will only copy non empty values from M&N columns to B&C

Answer 2

This piece of code should do the trick:

Sub CopyRangeToRange()
    Dim CpyFrom As Range
    Dim Cell As Range

    Set CpyFrom = ActiveSheet.Range("M11:N251")

    For Each Cell In CpyFrom
        If Cell.Value <> vbNullString Then
            Cell.Offset(0, -11).Value = Cell.Value
        End If
    Next Cell
End Sub