CODEWITHSUNDEEP
vbaexcel
Tainugget
Tainugget

Reputation: 3

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

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

Upvotes: 0

Views: 150

Answers (1)

dwirony
dwirony

Reputation: 5450

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.

Upvotes: 1

Related Questions