首页 文章

Vba代码，用于根据投递箱选择删除数据

提问于
浏览 次
0

如果选择了代码，我有一张带有列表框的工作表，如果选择了代码，则Excel将数据从工作表（具有相同的代码）复制到报价单中 .

如果我进行更改，请在同一个列表框中选择另一个代码，我需要excel去查找旧数据并在报价单中删除它 .

Public Sub delete_selected_rows（）Dim rng1 As Range，rng2 As Range，rngToDel As Range，c As Range Dim lastRow As Long

With Worksheets("Q")
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    Set rng1 = .Range("B1:B" & lastRow)
End With

Set rng2 = Worksheets("SO").Range("D35")
For Each c In rng1
    If Not IsError(Application.Match(c.Value, rng2, 0)) Then
        'if value from rng1 is found in rng2 then remember this cell for deleting
        If rngToDel Is Nothing Then
            Set rngToDel = c
        Else
            Set rngToDel = Union(rngToDel, c)
        End If
    End If
Next c

If Not rngToDel Is Nothing Then rngToDel.CurrentRegion.Delete

结束子

如何让CurrentRegion计算删除额外的30行

excel-vba vba excel

1 回答

  • 0

    VBA的ISERROR不会捕获由失败的MATCH工作表函数引起的错误 . 您需要以不同方式构造该部分 .

    Public Sub delete_selected_rows()

    Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
    Dim lastRow As Long
    Dim R As Long

    With Worksheets("Q")
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set rng1 = .Range("B1:B" & lastRow)
    End With

    Set rng2 = Worksheets("SO").Range("D35")
    For Each c In rng1
        On Error Resume Next
        R = 0
        R = WorksheetFunction.Match(c.Value, rng2, 0)
        On Error GoTo 0
        If R Then
            'if value from rng1 is found in rng2 then remember this cell for deleting
            ' R is the row number in rng2 where a match was found
            ' since rng2 is a single cell, R would always be 1, if found
            ' If rng2 = D35 MATCH be an overkill. Why not simply compare?
        Else
            If rngToDel Is Nothing Then
                Set rngToDel = c
            Else
                Set rngToDel = Union(rngToDel, c)
            End If
        End If
    Next c

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

    请注意我对rng2的评论 . 会有一些错误吗？ SO.D35含有什么？如果它包含一个值字符串，其中一个值可能是您查找MATCH的值是错误的函数 .

    您似乎打算将所有要删除的项目都删除，并在最后一次删除它们 . 我__22329_对我迟到了 . 更常见的方法是一次删除一行，就像找到它们一样，因为一旦删除了一行，该行下面的所有行号都会改变 . 您可以在关闭ScreenUpdating的情况下运行整个代码，并在完成所有删除后设置 Application.ScreenUpdating = True .

    回复于

相关问题