excel - 删除 FindNext 循环中的行的问题

Question

使用此代码，我尝试搜索包含逗号字符的列中的单元格，并将其分成 2 个新单元格。

Next 我想删除原始行，但似乎不可能，因为该值用于FindNext操作。

我有什么:

Column D       Column E
Carrot         Vegetable 
Apple,Banana   Fruit

我需要什么:

Column D       Column E
Carrot         Vegetable 
Apple          Fruit
Banana         Fruit

我做了什么:

Sub newentry()
'
' newentry Macro
'

Dim line
Dim col
Dim content

With Sheets("Feuil3").Columns("D")
    Set c = .Find(",", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do

        c.Select
            line = ActiveCell.Row
            col = ActiveCell.Column
            content = ActiveCell
            category = Cells(line, "E")

            Dim Table() As String
            Dim i As Integer

            'split content in a table
            Table = Split(content, ",")

            'loop on table
            For i = 0 To UBound(Table)
            'copy result on next line
                Rows(line + 1).Insert
                Tableau(i) = Application.WorksheetFunction.Trim(Table(i))
                Cells(line + 1, col).Value = Table(i)
                Cells(line + 1, "E").Value = category


                Next i

                Set c = .FindNext(c)

                If c Is Nothing Then
                    GoTo DoneFinding
                End If
                 'where/how to do this ?
                 Rows(c.Row).Delete Shift:=xlUp         
            Loop While Not c Is Nothing And c.Address <> firstAddress

        End If
DoneFinding:
    End With
End Sub

如何删除刚刚找到的行？

谢谢。

Answer 1

假设我们在 D 列中有数据，例如:

运行这个短宏:

Sub Restructure()
    Dim N As Long, i As Long, j As Long
    Dim arr1, arr2, arr3, a1, s As String

    N = Cells(Rows.Count, "D").End(xlUp).Row
    j = 1
    arr1 = Range("D1:D" & N)

    For Each a1 In arr1
        s = Mid(a1, 2, Len(a1) - 2)
        If InStr(s, ",") = 0 Then
            Cells(j, "E").Value = "[" & s & "]"
            j = j + 1
        Else
            arr2 = Split(s, ",")
            For Each a2 In arr2
                Cells(j, "E").Value = "[" & a2 & "]"
                j = j + 1
            Next a2
        End If
    Next a1
End Sub

将在E列中生成此内容:

注意:

原始数据未受到干扰。