[RESOLVED] Excell: converting comma-separated array

[arseniy]

Hey,

Thank you very much for your help guys!

Keywords:

cell, replace, Visual, Basic, Excell, delete, row, content, array, comma, separated, covert, automate

Here is what I made in the end:

Option Explicit
 
Sub arseniy_commas()
Const sSeparator As String = ", " 'The value to be used to split the cells
 
Dim rngSource As Range
Dim saResults() As String
Dim numberRows As Integer
Dim lItemNum As Long
 
    'Start in cell C1
    Set rngSource = ThisWorkbook.Worksheets(1).Range("C1")
    
    'Loop through all used cells in Column C
    Do While rngSource.Value <> ""
        
        saResults = Split(rngSource.Value, sSeparator)
        
        numberRows = UBound(saResults)
        
        rngSource.Offset(1).EntireRow.Resize(numberRows).Insert
        rngSource.EntireRow.Copy rngSource.EntireRow.Offset(1).Resize(numberRows).EntireRow
        
        On Error Resume Next
                            
        'Loop through each item in the array
        For lItemNum = LBound(saResults) To UBound(saResults)
            'Writing the value to the next cell below the source cell
            rngSource.Offset(lItemNum, 0).Value = saResults(lItemNum)
        Next lItemNum
           
 
           
        'Move to the next source cell in column
        Set rngSource = rngSource.Offset(lItemNum, 0)
        
    Loop
 
    Set rngSource = Nothing
End Sub

Attached is the example of how it works.

Cheers!