Split comma delimited fields into rows copying all other cells into new rows. I have the following code so far

E

Estarriol

New Member
Joined
Jul 26, 2005
Messages
29
I have the following code courtesy of Rick Rothstein. I've edited it a bit, to change columns used from D & F to B & N
and added comments (trying to follw the code he wrote, the comments may be wrong and if anyone could fix it would be appreciated)
My problem is that there are some dates in columns P,Q,R,S,T
After running the code, the dates don't match what they should be.
(other columns, other data, which *seems* to copy ok at the moment)
Please ignore all dots, they are spacers for this post.
There are more than 1 row. Some may contain only 1 field in B and 1 field in N.
B and N will have the same number of comma delimited fields.
eg: Before =
Column B...........................Column N.........................................................Column P........Column Q........Column R........Column S........Column T
AU1234, AU1235, AU1236.....M98765 (40AB), M87654 (40AC), M76543 (30AD)....2013/03/19.....2013/03/17.....2013/04/27.....2013/04/23.....2013/04/23

After: SHOULD BE:
Column B...........................Column N.........................................................Column P........Column Q........Column R........Column S........Column T
AU1234.............................M98765 (40AB).................................................2013/03/19.....2013/03/17.....2013/04/27.....2013/04/23.....2013/04/23
AU1235.............................M87654 (40AC).................................................2013/03/19.....2013/03/17.....2013/04/27.....2013/04/23.....2013/04/23
AU1236.............................M76543 (30AD).................................................2013/03/19.....2013/03/17.....2013/04/27.....2013/04/23.....2013/04/23

But, What I'm actually getting is:
Column B...........................Column N.........................................................Column P........Column Q........Column R........Column S........Column T
AU1234.............................M98765 (40AB).................................................2013/03/19.....2013/03/17.....2013/04/27.....2013/04/23.....2013/03/30
AU1235.............................M87654 (40AC).................................................2013/03/19.....2013/03/17.....2012/02/15.....2013/04/23.....2013/04/23
AU1236.............................M76543 (30AD).................................................2013/05/22.....2013/03/17.....2013/04/07.....2013/04/23.....2013/04/23

as you can see, some of the dates are correct, but there appear to be some random dates also.

Also, at the end of the code, after everything has been split to multiple rows, I would like to put a space between the letters and numbers in Column B
eg: AU 1234
AU 1235
but that's not too important, I can do without that if the Comma split is working correctly.
I can email sample .csv's if required for testing and code changing purposes.

Tia
Al

Code: 
Dim R As Long, C As Long, X As Long, LastRow As Long, LastColumn As Long, RowCount As Long
  Dim Index As Long, Data As Variant, RowsOut As Variant, ItemsB() As String, ItemsN() As String
'Find last row from Column B eg:100
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
'Find last column from Row 2 eg: Y
  LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
'Set data to be range A2 to Y100
  Data = Range("A2").Resize(LastRow - 1, LastColumn)
'Start on Row 2
  Index = 2
'Start with 1 and go to end of data (not sure what UBound is? does it mean entire array? every cell?)
  For R = 1 To UBound(Data)
'?????? somehow split based on "comma space" or "comma" (Data(R, 2) = Row, Column ? not sure why doing Row, Column when always used to thinking in Column, Row
    ItemsB = Split(Replace(Data(R, 2), ", ", ","), ",")
    ItemsN = Split(Replace(Data(R, 14), ", ", ","), ",")
'I'm lost here
    RowCount = UBound(ItemsB) + 1
    ReDim RowsOut(1 To RowCount, 1 To LastColumn)
    For X = 1 To RowCount
      For C = 1 To LastColumn
        RowsOut(X, C) = Data(R, C)
      Next
      RowsOut(X, 2) = ItemsB(X - 1)
      RowsOut(X, 14) = ItemsN(X - 1)
    Next
    Cells(Index, "A").Resize(RowCount, LastColumn) = RowsOut
    Index = Index + RowCount
'I find myself again, go back up to next R
  Next
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Found the problem!
Row 1 has column titles (this is from a CSV) and has more columns than Row 2. Some of the lower rows (3 to yyy) may have all columns filled in, Row 2 does not have all columns filled in.
in the code: it is
Rich (BB code): 
'Find last column from Row 2 eg: Y
  LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column

it needs to be:
Rich (BB code): 
'Find last column from Row [highlight][size=+2]1[/size][/highlight] eg: Y
  LastColumn = Cells([highlight][size=+2]1[/size][/highlight], Columns.Count).End(xlToLeft).Column


Estarriol said:
I have the following code courtesy of Rick Rothstein. I've edited it a bit, to change columns used from D & F to B & N
and added comments (trying to follw the code he wrote, the comments may be wrong and if anyone could fix it would be appreciated)
My problem is that there are some dates in columns P,Q,R,S,T
After running the code, the dates don't match what they should be.
(other columns, other data, which *seems* to copy ok at the moment)
Please ignore all dots, they are spacers for this post.
There are more than 1 row. Some may contain only 1 field in B and 1 field in N.
B and N will have the same number of comma delimited fields.
eg: Before =
Column B...........................Column N.........................................................Column P........Column Q........Column R........Column S........Column T
AU1234, AU1235, AU1236.....M98765 (40AB), M87654 (40AC), M76543 (30AD)....2013/03/19.....2013/03/17.....2013/04/27.....2013/04/23.....2013/04/23

After: SHOULD BE:
Column B...........................Column N.........................................................Column P........Column Q........Column R........Column S........Column T
AU1234.............................M98765 (40AB).................................................2013/03/19.....2013/03/17.....2013/04/27.....2013/04/23.....2013/04/23
AU1235.............................M87654 (40AC).................................................2013/03/19.....2013/03/17.....2013/04/27.....2013/04/23.....2013/04/23
AU1236.............................M76543 (30AD).................................................2013/03/19.....2013/03/17.....2013/04/27.....2013/04/23.....2013/04/23

But, What I'm actually getting is:
Column B...........................Column N.........................................................Column P........Column Q........Column R........Column S........Column T
AU1234.............................M98765 (40AB).................................................2013/03/19.....2013/03/17.....2013/04/27.....2013/04/23.....2013/03/30
AU1235.............................M87654 (40AC).................................................2013/03/19.....2013/03/17.....2012/02/15.....2013/04/23.....2013/04/23
AU1236.............................M76543 (30AD).................................................2013/05/22.....2013/03/17.....2013/04/07.....2013/04/23.....2013/04/23

as you can see, some of the dates are correct, but there appear to be some random dates also.

Also, at the end of the code, after everything has been split to multiple rows, I would like to put a space between the letters and numbers in Column B
eg: AU 1234
AU 1235
but that's not too important, I can do without that if the Comma split is working correctly.
I can email sample .csv's if required for testing and code changing purposes.

Tia
Al

Rich (BB code): 
Dim R As Long, C As Long, X As Long, LastRow As Long, LastColumn As Long, RowCount As Long
  Dim Index As Long, Data As Variant, RowsOut As Variant, ItemsB() As String, ItemsN() As String
'Find last row from Column B eg:100
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
'Find last column from Row 2 eg: Y
  LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
'Set data to be range A2 to Y100
  Data = Range("A2").Resize(LastRow - 1, LastColumn)
'Start on Row 2
  Index = 2
'Start with 1 and go to end of data (not sure what UBound is? does it mean entire array? every cell?)
  For R = 1 To UBound(Data)
'?????? somehow split based on "comma space" or "comma" (Data(R, 2) = Row, Column ? not sure why doing Row, Column when always used to thinking in Column, Row
    ItemsB = Split(Replace(Data(R, 2), ", ", ","), ",")
    ItemsN = Split(Replace(Data(R, 14), ", ", ","), ",")
'I'm lost here
    RowCount = UBound(ItemsB) + 1
    ReDim RowsOut(1 To RowCount, 1 To LastColumn)
    For X = 1 To RowCount
      For C = 1 To LastColumn
        RowsOut(X, C) = Data(R, C)
      Next
      RowsOut(X, 2) = ItemsB(X - 1)
      RowsOut(X, 14) = ItemsN(X - 1)
    Next
    Cells(Index, "A").Resize(RowCount, LastColumn) = RowsOut
    Index = Index + RowCount
'I find myself again, go back up to next R
  Next
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Cut and Paste certain cells of certain rows / if bored code optimization
Replies
0
Views
856
BobHex
B

Forum statistics

Threads
1,215,316
Messages
6,124,225
Members
449,148
Latest member
sweetkt327

Share this page

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