VBA to create new lines of data

jmewebb

New Member
Joined
Dec 6, 2012
Messages
43
Office Version
  1. 365
Platform
  1. Windows
1625664922813.png


Data separated by a comma in column "I" need to create a new row as shown under "Convert to:". The original data file already contains approximately 200k rows. I had another code but it took hours to run.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try this.
Change the input data address and the location of the first output cell
Note how it reads all the data into an array at the top, stores all the results in an array and writes it to the sheet in one line at the end.
You should try to read data once and write results only once, because every visit to the worksheet slows things down.

VBA Code:
Sub SplitText()
  Dim r, rows, i, j, s, Data
  Data = Range("Sheet1!A2:I6")  'change this to point to your data
  ReDim Result(1 To UBound(Data) * 1.5, 1 To 9) 'allows for 50% extra rows, increase if necessary

  For r = LBound(Data) To UBound(Data)
    s = Split(Data(r, 9), ",") 'split column I by commas
    For i = 0 To UBound(s)
      rows = rows + 1
      For j = 1 To 8
        Result(rows, j) = Data(r, j)
      Next j
      Result(rows, 9) = s(i)
    Next i
  Next r
  
  'change the range address below to point to the first cell you want filled with results
  Range("Sheet2!A1").Resize(rows, 9) = Result

End Sub
 
Upvote 0
I updated per your notes and receive a runtime error.
 
Upvote 0
What exactly was the error message that you received?
Did it give you the option to "Debug?
If so, what line of code does it highlight if you click the "Debug" button?
 
Upvote 0
What exactly was the error message that you received?
Did it give you the option to "Debug?
If so, what line of code does it highlight if you click the "Debug" button?
1625685698095.png
 
Upvote 0
Are your sheets named "Sheet1" and "Sheet2"?
If not, you will need to update those references in your code to match your sheet names.
 
Upvote 0
Can you compile the code?
If it compiles all right, tried stepping in to the code and going through the code line-by-line, pressing the F8 key, and see which line of code it errors out on.
That shows us what we need to focus on.
 
Upvote 0
Can you compile the code?
If it compiles all right, tried stepping in to the code and going through the code line-by-line, pressing the F8 key, and see which line of code it errors out on.
That shows us what we need to focus on.
ReDim Result(1 To UBound(Data) * 1.5, 1 To 9) 'allows for 50% extra rows, increase if necessary
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top