Excel宏-将逗号分隔为新行的条目 [英] Excel macro -Split comma separated entries to new rows

问题描述

我目前在工作表中有此数据

I currently have this data in a sheet

Col A   Col B   Col C
1       A       angry birds, gaming
2       B       nirvana,rock,band

我想做的是在第三列中拆分逗号分隔的条目，然后插入新行，如下所示:

What I want to do is split the comma separated entries in the third column and insert in new rows like below:

Col A   Col B   Col C
1       A       angry birds
1       A       gaming
2       B       nirvana
2       B       rock
2       B       band

我确信这可以通过VBA来完成，但我自己无法解决.

I am sure this can be done with VBA but couldn't figure it out myself.

推荐答案

使用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

来源:

结果:

这篇关于Excel宏-将逗号分隔为新行的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！