VBA to create new lines of data

jmewebb

New Member
Joined
Dec 6, 2012
Messages
42
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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
 

jmewebb

New Member
Joined
Dec 6, 2012
Messages
42
Office Version
  1. 365
Platform
  1. Windows
I updated per your notes and receive a runtime error.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

jmewebb

New Member
Joined
Dec 6, 2012
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are your sheets named "Sheet1" and "Sheet2"?
If not, you will need to update those references in your code to match your sheet names.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows
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.
 

jmewebb

New Member
Joined
Dec 6, 2012
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

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
Top