Hi guys,
I have a data set that needs some rows deleted if it does not match a list.
I will have 6 lists currently, but it could end up being more in the future.
The list will be on the "Delete List" tab, basically I want to let the user control what data should remain on the "Transfer Data" tab. So anything that is not on the Delete List, then the entire row will be deleted.
This code does this but it seems to take awhile, at least for the initial run, as this removes the bulk of the data. There is usually around 20k+ and this first run should remove around 65-70% of the data.
Then I have 5 more columns on the Delete List and I just repeated the code, adjusting it as I go.
Just wanted to see if there was a more faster/efficient way to code this. I tried using autofilter method bit couldnt get multiple criteria working.
Thanks.
I have a data set that needs some rows deleted if it does not match a list.
I will have 6 lists currently, but it could end up being more in the future.
The list will be on the "Delete List" tab, basically I want to let the user control what data should remain on the "Transfer Data" tab. So anything that is not on the Delete List, then the entire row will be deleted.
This code does this but it seems to take awhile, at least for the initial run, as this removes the bulk of the data. There is usually around 20k+ and this first run should remove around 65-70% of the data.
Then I have 5 more columns on the Delete List and I just repeated the code, adjusting it as I go.
Just wanted to see if there was a more faster/efficient way to code this. I tried using autofilter method bit couldnt get multiple criteria working.
VBA Code:
Dim cfind, cfind1, delete, c As Range
Dim x As String
Dim i, j As Integer
Set td = ThisWorkbook.Worksheets("Transfer Data")
Set dl = ThisWorkbook.Worksheets("Delete List")
Set cfind = dl.Cells.Find(what:="Location Name", lookat:=xlWhole)
Set delete = Range(cfind.Offset(0, 0), cfind.End(xlDown))
j = td.Range("H2").End(xlDown).Row
For i = j To 1 Step -1
x = td.Cells(i, "H").value
Set cfind1 = delete.Find(what:=x, lookat:=xlWhole)
If cfind1 Is Nothing Then
td.Cells(i, "H").EntireRow.delete
End If
Next
Thanks.