Delete row if specific cell value matches value found in another worksheet

[zicitroj]

Hi all,

Can anyone please help me this problem?

I need a formula or VBA macro to do the following:

I have 2 worksheets, namely sheet 1 and sheet 2.
Sheet 1 contains data in columns A to H.
Sheet 2 contains data only in column A.
I want Excel to check the data found in column A of Sheet 2 with those in Column A of Sheet 1. Hence, when an exact match is found in Column A of Sheet 1, Excel will delete that entire row.

Thanks a lot for your help!

regards,
Ali

 

[VoG]

Hi. Assuming that you want to delete the rows from Sheet1 try

Sub CheckA()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = LR To 1 Step -1
        If IsNumeric(Application.Match(.Range("A" & i).Value, Sheets("Sheet2").Columns("A"), 0)) Then .Rows(i).Delete
    Next i
End With
End Sub

 

[cchaynes]

Hi. Assuming that you want to delete the rows from Sheet1 try

Sub CheckA()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = LR To 1 Step -1
        If IsNumeric(Application.Match(.Range("A" & i).Value, Sheets("Sheet2").Columns("A"), 0)) Then .Rows(i).Delete
    Next i
End With
End Sub

This is great!

it works however i need help reversing the action. What i need to have happen is if the result is not found on sheet 2, then i want the entire row deleted, is that possible?

i have search various VB arguments and cant find how to rever match to something else.

Thanks!

 

[VoG]

Try

Sub CheckA()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = LR To 1 Step -1
        If IsError(Application.Match(.Range("A" & i).Value, Sheets("Sheet2").Columns("A"), 0)) Then .Rows(i).Delete
    Next i
End With
End Sub

 

[cchaynes]

thanks a million for the help this worked perfectly!

Great site and community here!

This is great!

it works however i need help reversing the action. What i need to have happen is if the result is not found on sheet 2, then i want the entire row deleted, is that possible?

i have search various VB arguments and cant find how to rever match to something else.

Thanks!

 

[Atmaster]

Hi,

I need a macro that can do exactly this except that it can do it throughout the whole workbook.

To give you more details about my situation:
I have a list with 35 unique serial numbers in Sheet 1.
These serial numbers are always found in column C, but are spread out throughout the entire workbook (between 30 and 100 different sheets)
I want the macro to find instances of the serial numbers in Sheet 1 and automatically delete the rows containing them in the rest of the workbook.

Ideally, when a match is found and a row is, the serial numbers in Sheet 1 will be highlighted.

Is this something you guys can help me out with?


Thanks!!

 

[VoG]

Hi

You have posted to an old thread. Many members including me are now "retired" from the board. Please start your own, new, thread.

Hi,

I need a macro that can do exactly this except that it can do it throughout the whole workbook.

To give you more details about my situation:
I have a list with 35 unique serial numbers in Sheet 1.
These serial numbers are always found in column C, but are spread out throughout the entire workbook (between 30 and 100 different sheets)
I want the macro to find instances of the serial numbers in Sheet 1 and automatically delete the rows containing them in the rest of the workbook.

Ideally, when a match is found and a row is, the serial numbers in Sheet 1 will be highlighted.

Is this something you guys can help me out with?


Thanks!!

 

[Atmaster]

Ok,

I have posted a new thread instead.
Thanks!