Excel macro -Split comma separated entries to new rows [duplicate]

by

variant using Scripting.Dictionary

Sub ttt()
    Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
    Dim x&, cl As Range, rng As Range, k, s
    Set rng = Range([C1], Cells(Rows.Count, "C").End(xlUp))
    x = 1 'used as a key for dictionary and as row number for output
    For Each cl In rng
        For Each s In Split(cl.Value2, ",")
            dic.Add x, Cells(cl.Row, "A").Value2 & "|" & _
                        Cells(cl.Row, "B").Value2 & "|" & LTrim(s)
            x = x + 1
    Next s, cl
    For Each k In dic
        Range(Cells(k, "A"), Cells(k, "C")).Value2 = Split(dic(k), "|")
    Next k
End Sub

source:

enter image description here

result:

enter image description here

More Related Contents:

  1. SumIf using Excel VBA
  2. How can I call a macro which contains a loop but only run one iteration of that loop?
  3. Test or check if sheet exists
  4. How can I find last row that contains data in a specific column?
  5. Copy from one workbook and paste into another
  6. How to copy Outlook mail message into excel using VBA or Macros
  7. Open Excel file for reading with VBA without display
  8. For Loop not fully cycling in excel VBA [duplicate]
  9. Excel VBA App stops spontaneously with message “Code execution has been halted”
  10. How to remove all non alphanumeric characters from a string except period and space in excel?
  11. Find last used cell in Excel VBA
  12. Does Dir() make any guarantee on the order of files returned?
  13. Closing Excel Application using VBA
  14. How to clear memory to prevent “out of memory error” in excel vba?
  15. Assign on-click VBA function to a dynamically created button on Excel Userform
  16. What are the benefits of setting objects to “Nothing”
  17. Using a wildcard to open an excel workbook
  18. VBA: Usage of parentheses for a method
  19. Fully reference a worksheet by codename
  20. OnTime for less than 1 second without becoming Unresponsive
  21. VBA equivalent to Excel’s mod function
  22. Get text from clipboard using GetText – avoid error on empty clipboard
  23. VBA editor auto-deletes spaces at the ends of lines
  24. Select entire column in table using Excel VBA
  25. Reducing WithEvent declarations and subs with VBA and ActiveX
  26. Application.WorksheetFunction.Match method
  27. Excel Hide/Show all tabs on Ribbon except custom tab
  28. Macros Not Showing Up in Macro Table
  29. Excel telling me my blank cells aren’t blank
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment