Redistribute a Delimited Column Of Data into Separate Rows (Keeping Other Data As Is)

**venkatgoutham** · 04-29-2013, 04:17 PM

Originally Posted by snb

Hi Rick

I tried to develop an alternative that doesn't need any writing into any worksheet. It will be done in memory completely (except writing the results of course).
You can see that I make use of all the special facilities that arrays offer us.
The macro is built on the example in this thread.

Code:

Sub M_snb()
    sn = Sheets(1).Cells(1).CurrentRegion
    
    For j = 1 To UBound(sn)
        c00 = c00 & "|" & Replace(String(UBound(Split(sn(j, 3), ",")), "|"), "|", j & "|") & j
    Next
    sq = Application.Index(sn, Application.Transpose(Split(Mid(c00, 2), "|")), Evaluate("transpose(row(1:" & UBound(sn, 2) & "))"))
    
    sp = Split(Join(Application.Transpose(Application.Index(sn, 0, 3)), ","), ",")
    For j = 0 To UBound(sp)
        sq(j + 1, 3) = sp(j)
    Next
    
    Cells(10, 1).Resize(UBound(sq), UBound(sq, 2)) = sq
End Sub

or another method:

Code:

Sub M_snb0()
    sn = Split(Join([transpose(A2:A6 & "_" & B2:B6 & "_" & substitute(C2:C6,", ","," & A2:A6 & "_" & B2:B6 & "_"))], ","), ",")
    Cells(20, 1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
    Cells(20, 1).CurrentRegion.TextToColumns , , , , False, False, False, False, True, "_"
End Sub

Hi my problem is same like DAT only. but this macro is not working for issue. when apply the macro it's showing error Run-time error '1004': Application-defined or object-defined error.

**Rick Rothstein** · 04-29-2013, 07:34 PM

Originally Posted by venkatgoutham

Hi my problem is same like DAT only. but this macro is not working for issue. when apply the macro it's showing error Run-time error '1004': Application-defined or object-defined error.

You quote the code that 'snb' posted in response to my article, so I'll let him deal with your question (whenever he next checks into this thread) as it relates to his code, but I was wondering if you tried the code I posted in main article and whether you had problems using it as well?

**venkatgoutham** · 04-30-2013, 03:46 PM

Originally Posted by Rick Rothstein

You quote the code that 'snb' posted in response to my article, so I'll let him deal with your question (whenever he next checks into this thread) as it relates to his code, but I was wondering if you tried the code I posted in main article and whether you had problems using it as well?

**Rick Rothstein** · 04-30-2013, 07:43 PM

Originally Posted by venkatgoutham

yep i tried. it's working fine which u posted in main article .yesterday it's working fine for me. but today i am getting error (which i told in previous quote). so i have to know one thing. which times that error come. what i need check whether it's error came.

Can you attach a copy of the workbook that is giving you the error so that I can test the code out on your exact data to see where the problem is located at?