PDA

View Full Version : [SOLVED] [ask]split text with comma to new rows



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

jolivanes
03-26-2015, 09:46 PM
Maybe

Sub vbaexpress_52132()
Dim j As Long, jve
For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
jve = Split(Cells(2, j), ",")
Cells(2, j).Resize(UBound(jve) + 1) = Application.Transpose(jve)
Next j
End Sub

qiyusi
04-08-2015, 06:29 PM
Hello, sorry for late reply.

The code works! thanks !