Split cell values into multiple rows and keep other data

by

Try this, you can easily adjust it to your actual sheet name and column to split.

Sub splitByColB()
    Dim r As Range, i As Long, ar
    Set r = Worksheets("Sheet1").Range("B999999").End(xlUp)
    Do While r.row > 1
        ar = Split(r.value, ",")
        If UBound(ar) >= 0 Then r.value = ar(0)
        For i = UBound(ar) To 1 Step -1
            r.EntireRow.Copy
            r.Offset(1).EntireRow.Insert
            r.Offset(1).value = ar(i)
        Next
        Set r = r.Offset(-1)
    Loop
End Sub

More Related Contents:

  1. Save excel file multiple times with file names in another workbook.
  2. Sequence of numbers based on start and end value of 2 cells [closed]
  3. Function to convert column number to letter?
  4. Read/Parse text file line by line in VBA
  5. Better way to find last used row
  6. Excel VBA: Answer gets “stuck”
  7. 2 Dimensional array from range [closed]
  8. filter out multiple criteria using excel vba
  9. Excel VBA autofilter all but three
  10. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  11. Using Google Chrome in Selenium VBA (Installation Steps)
  12. Dir() function not working in Mac Excel 2011 VBA
  13. Excel VBA code to copy a specific string to clipboard
  14. Delete a row in Excel VBA
  15. VBA: Convert Text to Number
  16. How can I run VBA code each time a cell gets its value changed by a formula?
  17. Using VBA to detect which decimal sign the computer is using
  18. Referencing Excel Userform Control Name Value From String (VBA)
  19. Fill formula down till last row in column
  20. If string contains word from list, return value adjacent to list value
  21. Refreshing all the pivot tables in my excel workbook with a macro
  22. Countif With Multiple OR Criteria
  23. Vlookup to copy color of a cell
  24. excel vba freeze pane without select
  25. How to delete rows in Excel based on criteria using VBA?
  26. Macro to export MS Word tables to Excel sheets
  27. VBA WinHTTP to download file from password proteced https website
  28. Delete all data rows from an Excel table (apart from the first)
  29. Excel Hide/Show all tabs on Ribbon except custom tab
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment