Delete Row IF condition is met, IF NOT proceed to next condition etc

[nremf]

Hi everyone,

I've been googling my heart out but still haven't found the VBA code / solution I am after.
Let's say I've got 2 spreadsheets named spreadsheet1 and spreadsheet2.

In spreadsheet1 there are hundreds of rows with relevant data and I have already turned the autofilter on.
The unique identifier used in spreadsheet1 is a column called IDNUMBER which contains a mixtures of letters and numbers, e.g. JT123GH5845, JT123GH5846, JT123GH5847, JT123GH5848 ...

In spreadsheet2 I have a list of IDNUMBERS that need to be taken off the list in spreadsheet1. So, if you find any of the IDNUMBERs listed in spreadsheet2 delete the row that contains that IDNUMBERS than proceed to the next. IF you can't find an IDNUMBER listed in spreadsheet2 proceed to the next one.

Example:
spreadsheet1 contains the above IDNUMBERs
IDNUMBER
JT123GH5845 - Row 1
JT123GH5846 - Row 2
JT123GH5847 - Row 3
JT123GH5848 - Row 4
JT123GH5860 - Row 5

spreadsheet2 contains the following IDNUMBERs
IDNUMBER
JT123GH5834
JT123GH5845
JT123GH5846
JT123GH5849
JT123GH5850

Therefore, the VBA code should get rid of Row 1 and Row 2 in spreadsheet1

As I did not know how to do a match up between the 2 spreadsheets I started off with deleting the individual entry by selecting the ID number and then just deleting the content of the row rather than the row itself, but it takes too long to maintain, now that the spreadsheet2 has become quite long. Furthermore, it seemed that, once an ID number wasn't found it skipped straight to the end of the macro rather than skipping to the next IDnumber. I probably have set the On Error Resume Next in the wrong position...

Sub CleanseAsPerTakeOffList()
'
' CleanseAsPerTakeOffList Macro
'
On Error Resume Next
'
' Open Spread Sheet: spreadsheet1
Sheets("spreadsheet1").Select
'
' Delete row in which a specific ID number is found via Filtering ID-Column
ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=8, Criteria1:= _
"JT123GH5834"
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
' Clear filter of ID column to restart process for new ID number
ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=8
'
End Sub

So, best case scenario would be spreadsheet1 gets cleansed according to a list in spreadsheet2 so that instead of constantly updating the macro I can just update spreadsheet2 and the macro just keeps on going until it's finished with cross-checking against spreadsheet2.

THANK YOU SO MUCH FOR YOUR HELP. This one has been bugging me for too long and none of the threads I googled showed me how to solve this, but maybe I am looking in the wrong direction, as I can usually find the VBA code I am after...

Nic

 

[AlphaFrog]

Hi and welcome to the forum. Try this...

[color=darkblue]Sub[/color] CleanseAsPerTakeOffList()
[color=green]'[/color]
[color=green]' CleanseAsPerTakeOffList Macro[/color]
[color=green]'[/color]
    
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range, vMatch [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] ws1 [color=darkblue]As[/color] Worksheet, ws2 [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]Set[/color] ws1 = Sheets("Sheet1")  [color=green]'Data sheet[/color]
    [color=darkblue]Set[/color] ws2 = Sheets("Sheet2")  [color=green]'List of IDs to delete[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] ws2.Range("A1", ws2.Range("A" & Rows.Count).End(xlUp)) [color=green]'ID list in column A[/color]
        vMatch = Application.Match(cell.Value, ws1.Columns(8), 0)           [color=green]'Match IDs in column 8[/color]
        [color=darkblue]If[/color] IsNumeric(vMatch) [color=darkblue]Then[/color] ws1.Rows(vMatch).Delete
    [color=darkblue]Next[/color] cell
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Delete rows complete."
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[oldbrewer]

												id	5
												id1
												id7
												id11
												id15
												id19
id	sales	profits
id2	5150	361
id3	5295	377				this macro found all matching id numbers and deleted the rows
id4	5440	393
id5	5585	409				if your list is on a different sheet, or in a different workbook
id6	5730	425
id8	6020	457				just adjust the cell references
id9	6165	473
id10	6310	489
id12	6600	521
id13	6745	537				For j = 2 To Cells(1, 14) + 1
id14	6890	553				tryid = Cells(j, 13)
id16	7180	585				For k = 45 To 11 Step -1
id17	7325	601				If Cells(k, 1) <> tryid Then GoTo 100
id18	7470	617				Rows(k).Delete
id20	7760	649				100 Next k
id6	7905	665				Next j
id8	8195	697				200 End Sub
id9	8340	713
id10	8485	729
id12	8775	761
id13	8920	777
id14	9065	793
id16	9355	825
id17	9500	841
id18	9645	857
id20	9935	889

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[nremf]

@AlphaFrog: That code was spot on. One Problem solved. Thank you SO very much

 

[AlphaFrog]

@AlphaFrog: That code was spot on. One Problem solved. Thank you SO very much

You're welcome. Thanks for the feedback.