我编写了一些简单的代码,将一个工作表中的单元格与另一个工作表中的单元格进行匹配,如果单元格相等,则删除整行。
该代码正确选择了行,但是由于某些原因拒绝实际删除我的工作表中的行。编辑:某些行删除。其他人则没有,即使它们的值与删除时的值完全相同。如果有人可以提供帮助,将不胜感激。
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