Hi Bob, as you say, a very poor explanation of what is required, but I
think the OP was after something like:


Code:
--------------------
Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i + 1, "C").Value = "" Then
'Cells(i, "B").Resize(, 2).Copy Cells(i - 1, "B")
Cells(i, "B").Value = Cells(i + 1, "A").Value
Cells(i, "C").Value = Cells(i + 1, "B").Value
Rows(i + 1).Delete
End If
Next i
--------------------


--

Bob Phillips Wrote:
That is hardly an explanation of the rules, just a re-statement of the
data.
Maybe this will do it, but maybe not, so if not, give us more help,
don't
leave us to second-guess what you are thinking

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, "A").Value = "" Then
Cells(i, "B").Resize(, 2).Copy Cells(i - 1, "B")
Rows(i).Delete
End If
Next i

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cy" wrote in message
...
oh no,iz like this

A B C
1 David 123 456
2 789 000
3 CK 135 246
4 CK 348 256
5 Steve abc efg
6 hij kem
to
A B C
1 David 789 000
2 CK 135 246
3 CK 348 256
4 Steve hij kem
"Bob Phillips" ??:

You need to explain the rules as to why the source should transform
to
the
target coz it ain't obvious to me.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cy" wrote in message
...
A B C A
B
C
1 David 123 456 to David 789
000
2 789 000 CK
135
246
3 CK 135 246 CK
348
256
4 CK 348 256 Steve
hij
kem
5 Steve abc efg
6 hij kem

Because i hv large amount of data,i don't want to copy and paste
1 by
1





--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=537563