Results 1 to 5 of 5

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

  1. #1
    Junior Member
    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

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

    Hi,

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

    Code:
    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:

    HTML Code:
    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.

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

    Thanks.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Your input table shows two columns that have delimited value. Can you post a sample of what is the expected output.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    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.

  5. #5
    Junior Member
    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0
    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.

Similar Threads

  1. Replies: 34
    Last Post: 03-13-2015, 02:26 PM
  2. Replies: 17
    Last Post: 05-22-2013, 11:58 PM
  3. Replies: 2
    Last Post: 03-05-2013, 07:34 AM
  4. Replies: 2
    Last Post: 06-14-2012, 04:10 AM
  5. Split Range into Multiple Columns VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 03-07-2012, 10:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •