Snasui Excel Forum

Code: Select all

Sub test1()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Cells(lr, 1).Select
If Cells(lr, 1) <> "" And Cells(lr, 2) <> "" Then
    Cells(lr, 3).Resize(1, 5).Copy Cells(lr + 1, 3)
    With Cells(lr, 3).Resize(1, 5)
        .Value = .Value
    End With
End If
End Sub

Code: Select all

Sub Test0()
    Dim l As Long, lf As Long
    With ActiveSheet
        l = .Range("a" & Rows.Count).End(xlUp).Row
        lf = .Range("c" & Rows.Count).End(xlUp).Row
        If l >= lf Then
            .Range("c" & lf).Resize(l - lf + 2, 5).FillDown
            .Range("c" & lf).Resize(l - lf + 1, 5) _
                = .Range("c" & lf).Resize(l - lf + 1, 5).Value
        End If
    End With
End Sub