时间戳

首页 > 解决方案 > 根据在另一个工作表中找不到的值删除行

excel - 根据在另一个工作表中找不到的值删除行

问题描述

我正在尝试执行以下操作：

  1. 将存储在工作表“State = Closed”的 B 列中的值（字符串）与另一个名为“Match List”的工作表的 A 列中的所有值进行比较。

  2. 删除“State = Closed”中与相应“Match List”值不匹配的任何行。

  3. 代码需要使用“匹配列表”中的任何长度（因为行数会改变）列表，以及任何长度的“状态 = 已关闭”工作表。

Sub ListRemove()
    Application.ScreenUpdating = False
    Dim i As Integer
    Dim b As Integer
    Dim Lastrow As Long
    Dim Lastrowb As Long
    Dim Del As Variant
    Worksheets("Match List").Activate
    Set Del = Range("A1:A67") '<--- This needs to be modified to work with any length Match List
    Lastrowb = Worksheets("State = Closed").Cells(Rows.Count, "A").End(xlUp).Row
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        For b = 1 To Lastrowb
            If Worksheets("State = Closed").Cells(i, 2).Value <> Del(b) Then
                Worksheets("State = Closed").Rows(i).EntireRow.Delete
            End If
        Next
    Next

    Application.ScreenUpdating = True
    Worksheets("State = Closed").Activate
End Sub

这将删除“状态 = 已关闭”工作表中的每一行，而不仅仅是匹配列表工作表中不包含相应值的行。

标签： excelvba

解决方案

在下面找到我的代码。如果第二张工作表的任何单元格中有相同的条目，则使用两个 for 循环检查每个值。

Sub List_Remove()
Dim i As Integer
Dim j As Integer
Dim k As Boolean
Dim shA As Worksheet
Dim shB As Worksheet

Set shA = Sheets("Sheet1") 'Worksheet that you want to compare with
Set shB = Sheets("Sheet2") 'Worksheet you want to delete rows from

For i = shB.UsedRange.Rows.Count To 1 Step -1
    k = False
    For j = 1 To shA.UsedRange.Rows.Count
        If shB.Cells(i, 1).Value = shA.Cells(j, 1).Value Then
           k = True
        End If
    Next
    If k = False Then
        shB.Rows(i).Delete
    End If
Next
EndSub

推荐阅读