将单元格值拆分为多行并保留其他数据 [英] Split cell values into multiple rows and keep other data

问题描述

B列中的值以逗号分隔.我需要将它们拆分为新行，并保持其他数据不变.

I have values in column B separated by commas. I need to split them into new rows and keep the other data the same.

我的行数可变.

我不知道B列的单元格中会有多少个值，因此我需要动态地遍历数组.

I don't know how many values will be in the cells in Column B, so I need to loop over the array dynamically.

示例:

ColA       ColB       ColC      ColD
Monday     A,B,C      Red       Email

输出:

ColA       ColB       ColC      ColD
Monday       A         Red       Email
Monday       B         Red       Email
Monday       C         Red       Email

尝试过类似的操作

colArray = Split(ws.Cells(i, 2).Value, ", ")
For i = LBound(colArray) To UBound(colArray)
    Rows.Insert(i)
Next i

推荐答案

尝试一下，您可以轻松地将其调整为实际的工作表名称和要拆分的列.

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

这篇关于将单元格值拆分为多行并保留其他数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！