This might be what you want.

Sub AA()
Dim rng As Range
Dim rng1 As Range
Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)).Offset(0, -1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.Formula = "=" & rng1(1).Offset(-1, 0).Address(0, 0)
rng.Formula = rng.Value
End If
End Sub


--
Regards,
Tom Ogilvy


"light" wrote in
message ...

T Rex

Thanks for trying. When I run the macro,it makes the cursor shakes
for the 10 seconds it runs and then only assigns 10 out of the 100
names .Deletes the other names in Column A.

Try to give another example.Names in Column A appear only once.
Column A4=Banana A5=Apple A6=Strawberry.

Columns B and C is the data for items in Column A.That data appears
multiple times. So data for Banana is in B4:B10 and C4:C10. Apple data
is in B11:B16 and C11:C16. Strawberry data is in B17:B24 and C17:C24.


So I need Banana to appear in A4:A10,Apple to appear in A11:A16
and Strawberry in A17:A24.


--
light
------------------------------------------------------------------------
light's Profile:
http://www.excelforum.com/member.php...fo&userid=7228
View this thread: http://www.excelforum.com/showthread...hreadid=399631