我编写了一些简单的代码,将一个工作表中的单元格与另一个工作表中的单元格进行匹配,如果单元格相等,则删除整行。

该代码正确选择了行,但是由于某些原因拒绝实际删除我的工作表中的行。编辑:某些行删除。其他人则没有,即使它们的值与删除时的值完全相同。如果有人可以提供帮助,将不胜感激。

Sub delFunds()
Dim fCell As Range 'Fund cell
Dim fRng As Range 'Fund range
Dim wCell As Range 'Working sheet cell
Dim wRng As Range 'Working sheet range
Dim n As Long

Set fRng = Worksheets("Funds").Range("C2:C117")
Set wRng = Worksheets("Working sheet").Range("I3:I7483")

For Each fCell In fRng.Cells 'Loop through all funds
    For Each wCell In wRng.Cells 'Loop through all working cells
        If StrComp(wCell.Value, fCell.Value, vbTextCompare) = 0 Then 'If equal then delete
            n = wCell.Row
            Rows(n & ":" & n).Select
            Selection.Delete Shift:=xlUp
        End If
    Next wCell
Next fCell 'Go to next fund

End Sub

最佳答案

我将使用以下代码而不使用嵌套循环:

Sub delFunds()
    Dim rngToDel As Range
    Dim fRng As Range 'Fund range
    Dim wCell As Range 'Working sheet cell
    Dim wRng As Range 'Working sheet range

    Set fRng = Worksheets("Funds").Range("C2:C117")
    Set wRng = Worksheets("Working sheet").Range("I3:I7483")

    For Each wCell In wRng 'Loop through all working cells
        ' if wCell found in Fund range then delete row
        If Not IsError(Application.Match(Trim(wCell.Value), fRng, 0)) Then
            If rngToDel Is Nothing Then
                Set rngToDel = wCell
            Else
                Set rngToDel = Union(rngToDel, wCell)
            End If
        End If
    Next wCell

    If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete
End Sub

10-05 21:23
查看更多