Sorry Bob,

I'm pretty new to vb for excel,

Further illustration:

A B C D
1 a Apple 12 as01
b
c
d
2 e Orange 80 os23
f
g
h
3 i Grape 200 gs44
j
k
l
and so on.
where "column A" will contain multiple value in a single cell, want to
split the single cell into multiple cells and duplicate the other cell
to the following lines,

Result:
A B C D
1 a Apple 12 as01
2 b Apple 12 as01
3 c Apple 12 as01
4 d Apple 12 as01
5 e Orange 80 os23
6 f Orange 80 os23
7 g Orange 80 os23
8 h Orange 80 os23
9 i Grape 200 gs44
10 j Grape 200 gs44
11 k Grape 200 gs44
12 l Grape 200 gs44

Is it possible to do so?

What is the "A" in LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row?
How can I run this to my excel worksheet? Place the cursor in A1, the
first cell contains multiple values?
tia,
Huang


On 8$B7n(B13$BF|(B, $B2<8a(B7$B;~(B37$BJ,(B, "Bob Phillips" wrote:
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

.Cells(i, "A").TextToColumns _
Destination:=.Cells(i, "A"), _
Other:=True, OtherChar:=Chr(10), FieldInfo:=Array(1, 1)
Next i

End With

With Application

.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mr_Huang" wrote in message

...

I have a string cell contained different values which is separated by
a carriage return (?)

like this:

domain/user1
domain/user2
pc/user1
pc/user2

how can I use formula to
1. split the single cell into multiple cells (4 different cells)
2. place a count to do a loop to duplicate other cells to the newly
created lines
thanks
huang