EXCEL VBA - 自動填值

Sub 自動填值設計()
i = 1

Range("A2").Select
Selection.End(xlDown).Select
x1 = ActiveCell.Row

Columns("A:A").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False



Range("A2").Select
While (i <> x1)
If ActiveCell.Value <> Empty Then
r1 = ActiveCell.Value
r2 = ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select


Else
ActiveCell.Value = r1
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = r2
ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(1, 0).Select
End If
i = i + 1

Wend

End Sub