Split Data in Table To Multiple Rows Based On Delimited Text In Columns

[nick5_86]

Hi,

I keep receiving error using this vba. I edited the code you provided to suit my worksheet to best of my knowledge.

Sub RedistributeData()
  Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String
  Const Delimiter As String = ","
  Const DelimitedColumn As String = "B"
  Const TableColumns As String = "A:P"
  Const StartRow As Long = 2
  Application.ScreenUpdating = False
  LastRow = Columns(TableColumns).Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  For X = LastRow To StartRow Step -1
    Data = Split(Cells(X, DelimitedColumn), Delimiter)
    If UBound(Data) Then
      Intersect(Rows(X + 1), Columns(TableColumns)).Resize(UBound(Data)).Insert (xlShiftDown)
    End If
    Cells(X, DelimitedColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
  Next
  LastRow = Cells(Rows.Count, DelimitedColumn).End(xlUp).Row
  On Error GoTo NoBlanks
  Set Table = Intersect(Columns(TableColumns), Rows(StartRow).Resize(LastRow - StartRow + 1))
  On Error GoTo 0
  For Each A In Table.SpecialCells(xlBlanks).Areas
    A.FormulaR1C1 = "=R[-1]C"
    A.Value = A.Value
  Next
NoBlanks:
  Application.ScreenUpdating = True
End Sub

My table is somthing like this:

A                          B                                         C                                       D
L1A1                               L1B1,L1B2                             L1C1                                  L1D1,L1D2
L2A1                               L2B1,L2B2                             L2C1                                  L2D1,L2D2

I don't know where i get it wrong. When i click Debug, this line is highlighted.

Intersect(Rows(X + 1), Columns(TableColumns)).Resize(UBound(Data)).Insert (xlShiftDown)

Thanks.

[Excel Fox]

Hi Nick. Welcome to ExcelFox.

Please start a new thread and do not hijack, unless your query is exactly the same as being discussed. If it is a related query, just post the link to that page in the thread that you started. I have moved your post to a new thread.

[Excel Fox]

Your input table shows two columns that have delimited value. Can you post a sample of what is the expected output.

[Rick Rothstein]

Your input table shows two columns that have delimited value. Can you post a sample of what is the expected output.

Actually, I cannot tell what is in his columns when I paste them into a worksheet (I get some merged cells resulting). A suggestion to nick5_86... list the cells and next to them their content, something like this...

C1 - L1A1
C2 - L1C1
etc. for Column C

D1 - L1B1,L1B2
D2 - L1D1,L1D2
etc. for Column D

Although use your actual values as I am not sure the above is correct. Then show us you expected output in the same way.

[nick5_86]

I'm very sorry for the hijack.

Sample Link

The sample sheet is the sort of data arrangement I have. Most are like the 1st row where the number of csv is the same in at least 2 cells. There are also some rows similar to the 2nd row.

The result page is the type of result I hope to achieve.

I understand that using the codes you provided, I can separate those similar to the 2nd row. But the single sheet that I hope to edit contains both type of rows.
Thanks.