Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

how modify vba split

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » How to modify VBA to split multiple cell?

How to modify VBA to split multiple cell?

ResolvedVersion 2007
Edited on Thu 8 Apr 2010, 01:42

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

Tue 20 Apr 2010: Automatically marked as resolved.

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Keyboard Shortcuts to Add Rows or Columns

Couple of other keyboard shortcuts. Shift+spacebar selects a row, Ctrl+spacebar selects a column. Select either row or column (or several) and use Ctrl and + to insert or Ctrl and - to delete rows or columns.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.