Gen has attended:
No courses
How to modify VBA to split multiple cell?
I have a code that I got from the web.
It works fine with my first document since I only have Column B to split. But with the second document I have, I think I need to make some modification.
My questions are:
1. What if I have to spilt multiple columns? such as G, H, I, J,K. like in the one attached, how will I modify my VBA code below?
2. When I ran this code, the data that I split was transferred to a new worksheet. How will I modify the code if I want the data to stay at the same worksheet?
Thanks in advance!!!
Sub CellSplitter1()
Dim Temp As Variant
Dim CText As String
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim iColumn As Integer
Dim lNumCols As Long
Dim lNumRows As Long
iColumn = 2
Set wksSource = ActiveSheet
Set wksNew = Worksheets.Add
iTargetRow = 0
With wksSource
lNumCols = .Range("IV1").End(xlToLeft).Column
lNumRows = .Range("A99000").End(xlUp).Row
For J = 1 To lNumRows
CText = .Cells(J, iColumn).Value
Temp = Split(CText, Chr(10))
For K = 0 To UBound(Temp)
iTargetRow = iTargetRow + 1
For L = 1 to lNumCols
If L <> iColumn Then
wksNew.Cells(iTargetRow, L) _
= .Cells(J, L)
Else
wksNew.Cells(iTargetRow, L) _
= Temp(K)
End If
Next L
Next K
Next J
End With
RE: How to modify VBA to split multiple cell?
Hi Gen
Thanks for your post and accompanying code. This forum is mainly for questions that can be resolved without further need to review actual files.
In this case to give you an answer with confidence we would really need to see your working files and as you can appreciate this would take a couple of hours to reach a solution. This will be billable time with a subject expert and if you wish to investigate this further please let us know.
Kind regards
Jacob