根据其他工作表条件删除行 [英] Delete rows based on other sheet criteria

问题描述

条件表如下

 删除（Y / N）参考ID 
 Y 1 
 N 2 
 Y 3 
 Y 4 
 N 5 
  

数据表如下

 名称参考ID 
约翰1 
安迪2 
玛丽3 
安娜4 
罗尼5 
  

根据条款表中的删除（Y / N）列，我必须删除John，Mary和Anna，参考ID为1 ，3和5

我看了另一个解决方案示例这里，但条件似乎来自同一张表。我不知道如何根据适用的参考资料从另一张表中进行工作。

有关如何执行此操作的任何建议。

解决方案

使用是构建ReferenceID的字典，然后使用xlFilterValues参数在字典键上构建AutoFilter。

  Option Explicit 
 
 Sub qwewqw（）
 Dim d As Long，dict As Object 
 
设置dict = CreateObject（Scripting.Dictionary）
 dict.comparemode = vbTextCompare 
 
使用工作表（条件 ）
对于d = 2到.Cells（Rows.Count，B）。End（xlUp）.Row 
如果LCase（.Cells（d，A）。Value2）= y然后
 dict.Item（CStr（.Cells（d，B）。Value2））= .Cells（d，A）。Value2 
如果
， 
结束与
 
与工作表（数据）
如果.AutoFilterMode然后.AutoFilterMode = False 
带.Cells（1，A）。CurrentRegion 
 .AutoFilter字段：= 2，Criteria1：= dict.keys，运算符：= xlFilterValues 
带.Offset（1，0）.Resize（.Rows.Count  -  1，.Columns.Count）
如果CBool​​（Application.Subtotal（103，.Cells））然后
 .Cells.EntireRow.Delete 
结束如果
结束
 .AutoFilter 
结束
结束
结束Sub 
  

奇怪的是，以这种方式使用字典的键要求将数字视为文本进行过滤。

I have two remove rows in a Sheet in Excel based on conditions from another Sheet.

The Conditional Sheet is as follows

Delete(Y/N) ReferenceID
Y           1
N           2
Y           3
Y           4
N           5

The Data Sheet is as follows

Name ReferenceID
John 1
Andy 2
Mary 3
Anna 4
Rony 5

Based on the Delete(Y/N) column form the Conditional Sheet I have to delete John, Mary and Anna, with reference ID 1,3 and 5

I looked at another solution example here, but the condition seem to be from the same sheet. I don't know how to make it work from another sheet based on the references that apply.

Any ideas on how to do this.

解决方案

Build of dictionary of ReferenceIDs with Yes and then AutoFilter on the dictionary keys with the xlFilterValues parameter. If there are visible rows, delete them.

Option Explicit

Sub qwewqw()
    Dim d As Long, dict As Object

    Set dict = CreateObject("Scripting.Dictionary")
    dict.comparemode = vbTextCompare

    With Worksheets("Conditional")
        For d = 2 To .Cells(Rows.Count, "B").End(xlUp).Row
            If LCase(.Cells(d, "A").Value2) = "y" Then
                dict.Item(CStr(.Cells(d, "B").Value2)) = .Cells(d, "A").Value2
            End If
        Next d
    End With

    With Worksheets("Data")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, "A").CurrentRegion
            .AutoFilter Field:=2, Criteria1:=dict.keys, Operator:=xlFilterValues
            With .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    .Cells.EntireRow.Delete
                End If
            End With
            .AutoFilter
        End With
    End With
End Sub

Curiously, using a dictionary's keys in this manner demands that you treat numbers as text for filtering.

这篇关于根据其他工作表条件删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！