Macro to delete rows based in the comparative of the columns between two sheets

try

Sub test()
    Application.ScreenUpdating = False
    With Sheets("master")
        .Columns(1).Insert
        With .Range("b5", .Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
            .Formula = "=isnumber(match(b5,report!a:a,0))"
            .Value = .Value
        End With
        With .Range("a5").CurrentRegion
            .AutoFilter 1, False
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
        .Columns(1).Delete
    End With
    Application.ScreenUpdating = True
End Sub

Originally Posted by jindon

try

Sub test()
    Application.ScreenUpdating = False
    With Sheets("master")
        .Columns(1).Insert
        With .Range("b5", .Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
            .Formula = "=isnumber(match(b5,report!a:a,0))"
            .Value = .Value
        End With
        With .Range("a5").CurrentRegion
            .AutoFilter 1, False
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
        .Columns(1).Delete
    End With
    Application.ScreenUpdating = True
End Sub

Hi guys and jindon,

I need to add one more sheet in the comparison, the sheet tab "orders". So the new condition is (see in bold): if the the number in the column A in the "Master" sheet is not in the first column in the "Report" sheet and in the "Orders" sheet, the entire row regarding this number in the "Master" sheet should be deleted.

Is that possible?!

Thanks guys

Yes, it is working.
Thanks jindon

UP

Somebody else can help on that?

Hi guys,

It is really important this macro... just need to update...
Somebody can help me?

Hi everybody,

Somebody able to help me on that?!

I really appreciate

Up!

Please somebody give a hand

Hi everybody,
Somebody could help me on this macro?!
The update looks simple but I am not sure how to do it: if the the number in the column A in the "Master" sheet is not in the first column in the "Report" sheet and in the "Orders" sheet, the entire row regarding this number in the "Master" sheet should be deleted.

And the code is

Sub test()
    Application.ScreenUpdating = False
    With Sheets("master")
        .Columns(1).Insert
        With .Range("b5", .Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
            .Formula = "=isnumber(match(b5,report!a:a,0))"
            .Value = .Value
        End With
        With .Range("a5").CurrentRegion
            .AutoFilter 1, False
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
        .Columns(1).Delete
    End With
    Application.ScreenUpdating = True
End Sub

Hi everybody,
Hi Jindon , AB33

Any solution for this one?!
Thank you!

maybe so

...
        With .Range("b5", .Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
            .Formula = "=--AND(ISNUMBER(MATCH(b5,Report!a:a,0)),ISNUMBER(MATCH(b5,Orders!a:a,0)))"
            .Value = .Value
        End With
        With .Range("a5").CurrentRegion
            .AutoFilter 1, 0
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
        ...

Originally Posted by nilem

maybe so

...
        With .Range("b5", .Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
            .Formula = "=--AND(ISNUMBER(MATCH(b5,Report!a:a,0)),ISNUMBER(MATCH(b5,Orders!a:a,0)))"
            .Value = .Value
        End With
        With .Range("a5").CurrentRegion
            .AutoFilter 1, 0
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
        ...

Hi nilem,
Thank you for your reply.

The macro is actually deleting everything, it is clearing the whole master sheet ("Schedule" sheet).
I attached a sample sheet showing exactly what I mean:
SAMPLE 19-08.xlsm

The macro you gave me is in the file. When you run it, it clears everything.

Any solution for this?!

I really appreciate

try it

.Formula = "=--OR(ISNUMBER(MATCH(B4,'Sap Report'!A:A,0)),ISNUMBER(MATCH(B4,Orders!A:A,0)))"

It is working.
Thank you