vba - Excel 2010 merge columns by inserting new rows -

I have a large CSV file (approximately 10,000 rows) that I want to upload to optimize for a new layout Required

This is currently in a format:

  index 1 heading 1 1,2,3,4 option 1a, other data index 2 heading 2 1 , 2,3 Option 2A, B, C, D, Other Data Index 3 Blank Blank Other Data Index 4 Title 4 1,2 Other Blank Other Data   
 G looks like.  
  Index1 Title1 1 Option 1A Other Data 2B3 4 Index 2 Title2 1 Option 2A Other Data 2B3CD Index 3 Other Data Index 4 Title 4 1 Other Data 2   
 I understand VBA only at the base level, and columns are not necessarily ABCD, so if the macro is specified that where the column is specified, then it will be very helpful.   
 
 
 It should do something like what you need. Although I have made the impression that your file tab is delimited. It reads the file in using FileSystemObject, for which you must add a reference to the Microsoft scripting runtime, Tools & gt; References and make sure it is checked. I have commented on where it is for specific column numbers, but you should do what you should do.    New FileSystemObject dims TextStream I = 0 set ts = fs.OpenTextFile ("file.csv") as in the form of a side where not ts.AtEndOfStream textline = split ( Ts.ReadLine, Chr (9) range ("A1") offset (i). Resize (1, 6). Value = Textline 'assumes that there are six columns in the file NewCol1 = Split (Textline (2),', ')' Split in an Array 3 words (2 as it is zero based) NewCol2 = Split (textline (4 ), '5', 'RowCount1, RowCount1, RowCount2' as an Array RowCount1 = UBound (NewCol1) RowCount2 = UBound (NewCol2) in MAXCOUNT = IIf ("RowCount2, RowCount1, RowCount2") is the biggest search of two line counters That we want to take down many of these rows: RowCount1 & gt; . 0 then range ("A1") offsets (i, 2) .Resize (RowCount1 + 1, 1) = WorksheetFunction.Transpose (NewCol1) 'RowCount2 & gt; array is placed in vertical 3 columns; 0 then range ("A1"). Offset (i, 4) .Resize (RowCount2 + 1, 1) = WorksheetFunction.Transpose (NewCol2) 'Keep the array in vertical 5 columns (4 with cell A1) I = I + MAX <1 VAND