This worked for me--but it destroys the original worksheet. Save before you run
it and close without saving if it doesn't work:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim newWks As Worksheet
Dim ValuesToCopy As Long
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With wks.Range("b:b")
.Cells.TextToColumns Destination:=.Columns(1), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False
End With

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
'
' newWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
' newWks.Cells(oRow, "B").Value = .Cells(oRow, "B").Value

ValuesToCopy = Application.CountA(.Range(.Cells(iRow, "B"), _
.Cells(iRow, .Columns.Count)))

If ValuesToCopy = 0 Then
ValuesToCopy = 1
End If

newWks.Cells(oRow, "A").Resize(ValuesToCopy, 1).Value _
= .Cells(iRow, "A").Value

.Cells(iRow, "B").Resize(1, ValuesToCopy).Copy
newWks.Cells(oRow, "B").PasteSpecial Transpose:=True

oRow = oRow + ValuesToCopy
Next iRow
End With
End Sub

swchee wrote:

Hi, I hv an excel file which contain 2 colums, the first column is
STATE and 2nd column is zipcode. The 2nd column contain a list of
zipecodes seperated by ",".
My question : how do I separet each of the zipcode and copy it along
with column 1 to another worksheet in the same w/book?

Eg : the source file =

state1 | 5500, 5511, 5522
state2 | 6300, 6800
state3 | 45000
state4 |
state5 | 4100, 4200

the expected file =

state1 | 5500
state1 | 5511
state1 | 5522
state2 | 6300
state2 | 6800
state3 | 45000
state4 |
state5 | 4100
state5 | 4200

Thanks in advance.

--
swchee
------------------------------------------------------------------------
swchee's Profile: http://www.excelforum.com/member.php...o&userid=24279
View this thread: http://www.excelforum.com/showthread...hreadid=378942

--

Dave Peterson