|
Sub MyData_Split()
Dim i As Long, j As Long
Dim Ary1() As String, Ary2() As String
Dim SpAry As Variant, V As Variant
With Sheets("Sheet1")
For i = 1 To .Range("A1").End(xlDown).Row
If IsEmpty(.Cells(i, 2).Value) Then
ReDim Preserve Ary1(j): Ary1(j) = .Cells(i, 1).Value
ReDim Preserve Ary2(j): Ary2(j) = ""
j = j + 1
Else
If Len(.Cells(i, 2).Value) = 1 Then
ReDim Preserve Ary1(j): Ary1(j) = .Cells(i, 1).Value
ReDim Preserve Ary2(j): Ary2(j) = .Cells(i, 2).Value
j = j + 1
Else
SpAry = Split(.Cells(i, 2).Value, ",")
For Each V In SpAry
ReDim Preserve Ary1(j): Ary1(j) = .Cells(i, 1).Value
ReDim Preserve Ary2(j): Ary2(j) = V
j = j + 1
Next
Erase SpAry
End If
End If
Next i
End With
With Sheets("Sheet2")
.Cells(1, 1).Resize(UBound(Ary1) + 1).Value = _
WorksheetFunction.Transpose(Ary1)
.Cells(1, 2).Resize(UBound(Ary2) + 1).Value = _
WorksheetFunction.Transpose(Ary2)
.Cells(1, 1).CurrentRegion.Borders.LineStyle = 1
End With
Erase Ary1, Ary2
End Sub
で、どうかな ?
|
|