CODEWITHSUNDEEP
excelmatchvba
xyz
xyz

Reputation: 2300

Macro to delete column if match get error

I am searhing values in Col A of sheet "YYY" with values from Col A sheet"XXX" if a match is found delete the entire row of the matched cell on sheet"YYY"

I get object required on this line FindValues(i, 1).Row.Delete I have beeen tring to correct this for a while now but failing

Thanks Edit: updated

Sub FindReplace_Updated_Blanks()
Dim FindValues As Variant, SearchValues As Variant
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim sLR As Long, tLR As Long, i As Long

Set wsSource = ThisWorkbook.Worksheets("XXX")
Set wsTarget = ThisWorkbook.Worksheets("YYY")

          sLR = wsSource.Range("A" & wsSource.Rows.Count).End(xlUp).Row
          tLR = wsTarget.Range("A" & wsSource.Rows.Count).End(xlUp).Row
 SearchValues = wsSource.Range("A2:A" & sLR).Value
   FindValues = wsTarget.Range("A2:A" & tLR).Value

    For i = LBound(FindValues) To UBound(FindValues)
            If Not IsError(Application.match(SearchValues(i, 1), wsTarget.Range("A2:A" & tLR), 0)) Then
                wsTarget.Rows(i + 1).Delete
          End If
    Next i
End Sub

Upvotes: 0

Views: 44

Answers (1)

n8.
n8.

Reputation: 1738

Change it for:

Sheets("YYY").Rows(i + 1).Delete

Since your range starts with a static "2" you don't need to use the range to find the row.

Upvotes: 1

Related Questions