Compare 2 Worksheets based on single criteria and then delete row

[jejnycta]

Sheet1 in test.xlsm includes 2 columns listing different fruits (column A) and type (Column B). I would like to delete rows that do not equal "Mango". The "Mango" criteria is listed in Sheet1 in Criteria.xlsm. Problem: everything is deleted but the 2nd row that has fruit "Grape". What should happen is that only rows that don't have the fruit "Mango" should be deleted.

Here is my code and it is executed in Sheet1 in test.xlsm (Both files (test and Criteria) attached. Thank you so much!

Sub DeleteDifferentWorkbook()

Application.ScreenUpdating = False
Dim iRow As Integer
Dim lr As Long
Dim List As Variant
Dim y As Workbook

lr = ThisWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Set y = Workbooks.Open("C:\Users\jojoshu\Desktop\VBA Test\Criteria.xlsm")
List = y.Worksheets("Sheet1").Cells(2, "A").Value

For iRow = lr To 1 Step -1
If Cells(iRow, "A").Value <> List Then
ThisWorkbook.Worksheets("Sheet1").Rows(iRow).Delete
End If
Next iRow

Application.ScreenUpdating = True

End Sub

[Mumps1]

Try:

Please Login or Register  to view this content.

[jejnycta]

Getting error message
Error Message.JPG
Code with Error Line Highlighted.JPG

I would prefer to keep it simple by using the simple loop and "if and End if" methods instead of autofilter method

The following code works beautifully when the rows to delete data (Sheet1) and the Criteria (Sheet2) worksheets are in the same workbook:

Sub Delete()

Application.ScreenUpdating = False
Dim i As Integer
Dim lr As Long
Dim List As String

lr = Range("A" & Rows.Count).End(xlUp).Row
List = Worksheets("Sheet2").Cells(2, "A").Value

For i = lr To 1 Step -1
If Cells(i, "A").Value <> List Then
Worksheets("Sheet1").Rows(i).Delete
End If
Next i

Application.ScreenUpdating = True

End Sub

[jejnycta]

Getting error message
Attachment 606366
Attachment 606367

I would prefer to keep it simple by using the simple loop and "if and End if" methods instead of autofilter method

The following code works beautifully when the rows to delete data (Sheet1) and the Criteria (Sheet2) worksheets are in the same workbook:

Sub Delete()

Application.ScreenUpdating = False
Dim i As Integer
Dim lr As Long
Dim List As String

lr = Range("A" & Rows.Count).End(xlUp).Row
List = Worksheets("Sheet2").Cells(2, "A").Value

For i = lr To 1 Step -1
If Cells(i, "A").Value <> List Then
Worksheets("Sheet1").Rows(i).Delete
End If
Next i

Application.ScreenUpdating = True

End Sub

[Mumps1]

Give this version a try:

Please Login or Register  to view this content.

[jejnycta]

Thank you Mumps1. After making a slight change to your code, it works perfectly. I would very much appreciate it if you could at your spare time (no rush) as to why my original code with the loop and if then statement did not work. As I stated to you earlier when executing code within same workbook the code works fine. What is it about executing the code with 2 workbooks that it does not work?

Once again thank you so much for your prompt response and assistance.

[jejnycta]

Thank you Mumps1. After making a slight change to your code, it works perfectly. I would very much appreciate it if you could at your spare time (no rush) look into as to why my original code with the loop and if then statement did not work. As I stated to you earlier when executing code within same workbook the code works fine. What is it about executing the code with 2 workbooks that it does not work?

Once again thank you so much for your prompt response and assistance.

[Mumps1]

You are very welcome. When you open a workbook, that workbook becomes the active workbook so any code that follows the "Open" line of code applies to the newly opened workbook. If you want to have the code apply to the "test.xlsm" workbook, then you have to fully qualify all the references to that workbook so this:

Please Login or Register  to view this content.

becomes this:

Please Login or Register  to view this content.

Note the dot notation (in red) which makes those lines refer to Sheet1 in "test.xlsm. I hope this helps.

[jejnycta]

Thanks for introducing me Autofilter methodolgy and also, that when working with multiple workbooks, statements have to be fully qualified. Not only did you do a beautiful job of providing the solutions but also the explanations to the problem/solution.

Awesome!!!!

[Mumps1]

You are very welcome. Looping can be very slow compared to filtering so if you have a large data set, it will make a difference.

[jejnycta]

What if my List referenced 10+ rows, would Autofilter work or would the loop method be more suitable for this task?

I made the following change but got type mismatch error (Runtime error 13):

List = y.Worksheets("Sheet1").Cells(2, "A").Value
List = y.Worksheets("Sheet1").Range("A2:A11").Value



Sub DelDifferentWkbk_Loop()

Application.ScreenUpdating = False
Dim iRow As Integer
Dim lr As Long
Dim List As String
Dim y As Workbook

lr = ThisWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Set y = Workbooks.Open("C:\Users\jojoshu\Desktop\STARS VBA Test\Criteria.xlsm")
List = y.Worksheets("Sheet1").Range("A2:A11").Value

With Workbooks("test.xlsm").Sheets("Sheet1")
For iRow = lr To 1 Step -1
If .Cells(iRow, "A").Value <> List Then
.Rows(iRow).Delete
End If
Next iRow
End With

Application.ScreenUpdating = True

End Sub

[Mumps1]

Are you saying that you want to delete rows based on more than one value?

[jejnycta]

Yes and those values have to reference a range of cells, say A1 to A11.

Thanks!

[Mumps1]

That's a little trickier. Can you post a copy of your file and explain in detail what you want to do? De-sensitize the data if necessary.

[jejnycta]

Sheet1 in test.xlsm includes 2 columns listing different fruits (column A) and type (Column B). I would like to delete rows in Sheet1 of test.xlsm file that do not equal the values listed in Sheet1 of Criteria.xlsm file.

[Mumps1]

Try:

Please Login or Register  to view this content.

[jejnycta]

After 2 minor adjustments, your code works fine. Thanks. Now I have to study it as you have introduced new terminology that I am unfamiliar with.

[Mumps1]

Here is the macro with some explanatory comments:

Please Login or Register  to view this content.