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?

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](https://stackoverflow.com/help/how-to-ask), then edit your question and be sure to ask a [good, clear, concise question](http://idownvotedbecau.se/unclearquestion), include the code, expected behaviour, and what is wrong... then we can try to help — Our Man in Bananas, Jul 23 '18 at 16:01
have a look at [How to delete multiple rows without a loop](https://stackoverflow.com/questions/15431801/how-to-delete-multiple-rows-without-a-loop-in-excel-vba/15431802#15431802) - you can just create a formula to mark rows as **deletable** then delete them, then remove the formula column — Our Man in Bananas, Jul 23 '18 at 16:02
also, what is `Range.Value` in `Set fn = sh2.Range("A:A").Find(Range.Value, , xlValues, xlWhole)` — Our Man in Bananas, Jul 23 '18 at 16:03
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, Jul 23 '18 at 16:11
have a look at my answer in [How to delete multiple rows without a loop in Excel](https://stackoverflow.com/questions/15431801/how-to-delete-multiple-rows-without-a-loop-in-excel-vba/15431802#15431802) — Our Man in Bananas, Jul 23 '18 at 16:13

score 1 · Accepted Answer · edited Jul 23 '18 at 18:05

1

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.

edited Jul 23 '18 at 18:05

Our Man in Bananas

5,809
21
91
148

answered Jul 23 '18 at 16:19

dwirony

5,487
3
21
43

After i changed that it is giving me "Invalid qualifier" for the line `sh1.Cells(c.Row, Columns.Count).EntireRow.Delete`. I believe it is the `(c.Row, Columns.Count)` part. I edited the full code with your suggestion for ease of viewing. – Tainugget Jul 23 '18 at 16:32
1

Change that to `sh1.Cells(c, 1).EntireRow.Delete`. Or `sh1.Rows(c).Delete` – dwirony Jul 23 '18 at 16:33

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

1 Answers1