选择行和删除不删除行 [英] Selecting row and deleting doesn't delete row

问题描述

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

代码正确选择行，但由于某种原因拒绝实际删除工作表中的行。编辑：某些行删除。其他人不会，即使它们具有与删除的值完全相同的值。如果有任何人可以帮助，将不胜感激。

  Sub delFunds（）
 Dim fCell As Range'Fund cell 
 Dim fRng As Range'基金范围
 Dim wCell As Range'工作单元格
 Dim wRng As Range'工作单范围
 Dim n As Long 
 
设置fRng =工作表（资金）范围（C2：C117）
设置wRng =工作表（工作表）。范围（I3：I7483）
 
每个fCell在fRng.Cells循环通过所有资金
每个wCell在wRng.Cells'循环通过所有工作单元
如果StrComp（wCell.Value，fCell.Value，vbTextCompare）= 0然后'如果等于然后删除
n = wCell.Row 
行（n&：& n）。选择
 Selection.Delete Shift：= xlUp 
如果
下一个wCell 
下一个fCell'转到下一个基金
 
 End Sub 
  

  Sub delFunds（）
 Dim rngToDel As Range 
 Dim fRng As Range'Fund range 
 Dim wCell As Range'工作单元格
 Dim wRng As Range'工作单范围
 
设置fRng =工作表（资金）范围（C2：C117）
设置wRng =工作表（工作表）。范围（I3：I7483）
 
对于每个wCell在wRng'循环通过所有工作单元
'如果wCell在基金范围中找到然后删除行
如果不是IsError（Application.Match（Trim（wCell.Value），fRng，0 ））然后
如果rngToDel不是然后
设置rngToDel = wCell 
 Else 
设置rngToDel = Union（rngToDel，wCell）
结束如果
结束If 
下一个wCell 
 
如果没有rngToDel是没有，然后rngToDel.EntireRow.Delete 
 End Sub 
  

I've written some simple code that matches cells in one worksheet to cells in another, and then deletes the entire row if the cells are equal.

The code selects rows properly, but for some reason refuses to actually delete the rows in my worksheet. EDIT: Some of the rows delete. Others don't, even though they have the exact same values as those that did delete. If anyone can help that would be greatly appreciated.

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

解决方案

I would use this code without nested loop:

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

这篇关于选择行和删除不删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！