Solved: extract a column to 5 column

[slamet Harto]

Hi there,

I need a vba code to extract a column to 5 column.

First Row data in Column K will be extract to column M
Second Row data in colum K will be extract to column N
and so on.

Thanks for assistance, rgds. Harto

See the attachement for you reference.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "K" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long
Dim Breaks As Variant
Dim NumItems As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To LastRow

Breaks = Split(.Cells(i, TEST_COLUMN).Value, Chr(10))
NumItems = UBound(Breaks) - LBound(Breaks) + 1
.Cells(i, TEST_COLUMN).Offset(0, 1).Resize(, NumItems) = Breaks
Next
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[slamet Harto]

Hi Bob,
Work fine, and thank you so much.
Your very helpful person.

Best regards, Harto

[georgiboy]

Too slow again... Here is where i got to just for info.

[vba]Function WrapToLines(rCell As Range, Line As Long)
Dim Str1 As Variant

Str1 = Split(Replace(rCell.Value, Chr(10), "~"), "~")

WrapToLines = Str1(Line - 1)

End Function[/vba]
Use like...

=WrapToLines(A1,1)
=WrapToLines(A1,2)
=WrapToLines(A1,3)
and so on

[slamet Harto]

Hi Georgiboy

Thanks so much, appreciate it.

Rgds, Harto