|
すいません、行数と列数は不明なのでしたね。これでやってみて下さい。
テストはうまくいきましたが。
Sub Data_Align()
Dim VAry As Variant, SAry As Variant
Dim i As Long, ER As Long
Dim x As Long, y As Long
With Sheets("Sheet1")
With .Range("A1").CurrentRegion
x = .Columns.Count: ER = .Rows.Count
End With
If .Columns(x).Find("*,*", , xlValues) Is Nothing Then
MsgBox x & " 列にカンマ区切りのデータがありません", 48
Exit Sub
End If
For i = 1 To ER
If Len(.Cells(i, x).Value) < 2 Then
VAry = .Cells(i, 1).Resize(, x).Value
Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1) _
.Resize(, x).Value = VAry
Else
SAry = Split(.Cells(i, x).Value, ",")
y = UBound(SAry) + 1
VAry = .Cells(i, 1).Resize(, x - 1).Value
With Sheets("Sheet2").Range("A65536").End(xlUp)
.Offset(1).Resize(y, x - 1).Value = VAry
.Offset(1, x - 1).Resize(y).Value = _
WorksheetFunction.Transpose(SAry)
End With
Erase SAry
End If
Next i
End With
End Sub
|
|