CODEWITHSUNDEEP
vbaexcelautofilter
Selrac
Selrac

Reputation: 2293

Delete rows based on other sheet criteria

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.

Upvotes: 1

Views: 969

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 2

Related Questions