qiyusi
03-26-2015, 05:49 PM
Hello Again,
I have a macro which work to split text with comma to new row, but not work properly. please review the code and screenshot below.
Here is the original data
13078
Here is the result after macro executed
13079
Here is the format i want
13080
macro code:
Sub split_rows()
Dim SearchRange As Range, CCell As Range, x As Long, y As Long, MyArr As Variant
Set SearchRange = Application.InputBox("Click in the column to split by", Type:=8)
For Each CCell In SearchRange.Cells
MyArr = Split(CCell, ",")
If UBound(MyArr) > 0 Then
CCell = MyArr(LBound(MyArr))
For x = LBound(MyArr) + 1 To UBound(MyArr)
CCell.Offset(x, 0).EntireRow.Insert
CCell.Offset(x, 0) = MyArr(x)
Next x
End If
Next CCell
End Sub
I am thinking how to change Entirerow.insert as this is causing problem when work to multiple cell with coma.
Appreciate for any respond.
Thanks
I have a macro which work to split text with comma to new row, but not work properly. please review the code and screenshot below.
Here is the original data
13078
Here is the result after macro executed
13079
Here is the format i want
13080
macro code:
Sub split_rows()
Dim SearchRange As Range, CCell As Range, x As Long, y As Long, MyArr As Variant
Set SearchRange = Application.InputBox("Click in the column to split by", Type:=8)
For Each CCell In SearchRange.Cells
MyArr = Split(CCell, ",")
If UBound(MyArr) > 0 Then
CCell = MyArr(LBound(MyArr))
For x = LBound(MyArr) + 1 To UBound(MyArr)
CCell.Offset(x, 0).EntireRow.Insert
CCell.Offset(x, 0) = MyArr(x)
Next x
End If
Next CCell
End Sub
I am thinking how to change Entirerow.insert as this is causing problem when work to multiple cell with coma.
Appreciate for any respond.
Thanks