Janmy wrote
Col A Col B Col C Col D
Row 1: 1/2/2004 1234 ABC ABC IS A MAN
Row 2: 1/3/2004 1234 ABC ABC MAN
Row 3: 2/3/2004 1234 ABC
Row 4: 4/3/2004 2345 ABC BBB

Then I need to put 'ABC IS A MAN' in Col D in row 2 and 3. And the
content remained the same in Col D for row 4.

You may try something along those lines:
Open the VB editor and enter following code for the Worksheet (not a
module!)

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
'target is the cellobject that was rightclicked
Cancel = True ' prevents the usual contextmenu to show
col = Target.Column
row = target.column
if col=4 then
lastval= Cells(row-1, col) 'value in the row above
Cells(Target.Row, col) = lastval
endif
End Sub
In your example: a rightclick on col D row 3 (now empty) will copy ABC MAN
in that cell

You may vary on this theme by inserting a For/Next, looking for a cell which
is not empty, etcetera

Alternatively, if you do not want to use this RightClick procedure, try
something like:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target = "*" Then
col = Target.Column
row = target.column
if col=4 then
lastval= Cells(row-1, col) 'value in the row above
Cells(Target.Row, col) = lastval
endif
endif
On Error GoTo 0
End Sub

In this example, if you type * in col D row 3 it will copy the value of the
cell above, at least if it happens in colums 4 (D)
Of course you may change * for any other printable character and/or use
other conditions.

HTH, Ko