[RESOLVED] Excell: converting comma-separated array

[arseniy]

Hello again DKenny, Webtest,

Thanks again for your support.

To get the situation clear, I attached an example of what I have and what I need to have.

This is how far I got:

I understood (but not much) the VBScript you advised and this is what I'm now fronting:

1) I have to insert N blank rows after currently analyzed cell, where N = last element in array of separated sources

2) I have to delete source row with comma-separated values after I have my parsing done

Overall syntax and concepts misunderstanding makes me to irk for your help again, however, this is what I tried:

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 rowsnumber As Long
Dim lItemNum As Long
 
    'Start in cell C3
    Set rngSource = ThisWorkbook.Worksheets(1).Range("C1")
    
    'Loop through all used cells in Column C
    Do While rngSource.Value <> ""
        
        saResults = Split(rngSource.Value, sSeparator)
        
        'HERE I tried to set rowsnumber = UBound(saResults) and get the number
        'of rows to be insterted
        'HERE I tried to use ThisWorkbook.Worksheets(1).addItem rowsbunmber
        'to insert blank rows of the interest
                      
        '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(1 + lItemNum, 0).Value = saResults(lItemNum)
        Next lItemNum
 
          
        'Move to the next source cell in column
        Set rngSource = rngSource.Offset(1 + lItemNum, 0)
 
        'HOW To delete entire row where we've just have had the source?
        'Like, ThisWorkbook.Worksheets(1).RemoveItem   ??
        'But the addressing will be hurt, isn't it?
 
        
    Loop
 
    Set rngSource = Nothing
End Sub

Thank you again for support,

I haven't even realized yesterday that I would go so far.