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
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
