Match value from sheet 1 A column to sheet 2 A column and delete the row if A value is not found

Question

The line "Set fn = sh2.Range("A:A").Find(Range.Value, , xlValues, xlWhole)" is giving me error "Argument not optional" on "Range.Value".

I want the code to move from last row to the top. Looking at Sheet1's column A's value. Then search for it in Sheet2's A column and if it is there then delete the entire row on Sheet 1. However I cant figure out the syntax/issues or is there better way to rewrite that line?

Thank you!

Full code(Edited):

Sub Isitthere()
Dim c As Long, LR As Long
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range
Set sh1 = Application.Sheets("Current") 'Edit sheet name
Set sh2 = Application.Sheets("MTD") 'Edit sheet name
LR = sh1.Range("A" & Rows.Count).End(xlUp).Row
For c = LR To 2 Step -1
    Set fn = sh2.Range("A:A").Find(sh1.Cells(c, 1).Value, , xlValues, xlWhole)
    If Not fn Is Nothing Then
            sh1.Cells(c.Row, Columns.Count).EntireRow.Delete
        End If
Next c
End Sub

Welcome to Stack Overflow: Please read How to ask a good question, then edit your question and be sure to ask a good, clear, concise question, include the code, expected behaviour, and what is wrong... then we can try to help — Our Man in Bananas
have a look at How to delete multiple rows without a loop - you can just create a formula to mark rows as deletable then delete them, then remove the formula column — Our Man in Bananas
also, what is Range.Value in Set fn = sh2.Range("A:A").Find(Range.Value, , xlValues, xlWhole) — Our Man in Bananas
I guess that's where i stuck on how to write the code. I basically want the line to say "If you can not find the A1/2/3/4/etc value in sheet 2 column A then mark it as "Nothing" so the If statement below that will delete the entire row. — Tainugget
have a look at my answer in How to delete multiple rows without a loop in Excel — Our Man in Bananas

dwirony dwirony · Accepted Answer · 2018-07-23T16:19:50

Range.Value (which is really Range().Value) isn't valid syntax because Range() is expecting an argument, and you're giving it none, which gives .Find an invalid argument as well.

Range.Value should either be sh1.Cells(c, 1).Value or sh1.Range("A" & c).Value.

Match value from sheet 1 A column to sheet 2 A column and delete the row if A value is not found

1 Answers