VBA - Delete Rows

[VbaHell]

Hello all

I hope there is a bonfin out there that can help me on this please

The code below deletes lines when the value "HIRECHARGE" is in column "F"
What I am trying to do is have a tab called "References" and then a list in Column "A" that could be looped through to delete all the lines in the data file.

IE:

HIRECHARGE
CARRIAGE
and so on

Is this possible please


"LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If InStr(1, Range("F" & i).Value, "HIRECHARGE") = 1 Then Rows(i).Delete
Next I"

 

[wideboydixon]

Public Sub DeleteReferences()

Dim lastRow As Long
Dim referenceList As Variant
Dim i As Long
Dim j As Long

With Worksheets("References")
    referenceList = .Range("A1", "A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
End With

For j = 1 To UBound(referenceList, 1)
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = lastRow To 2 Step -1
        If InStr(1, Range("F" & i).Value, referenceList(j, 1)) = 1 Then Rows(i).Delete
    Next i
Next j

End Sub

WBD

 

[VbaHell]

Wideboy thank you so much for your help on this, I have just put this together that works, it's a bit crude
Your code works is far more efficient and works quicker

Thanks again


Sub CleanupMIS()
Dim LR As Long
Dim i As Long
Dim j As Long
Sheets("MIS - Customer Invoice").Select
LR = Range("A" & Rows.Count).End(xlUp).Row
Sheets("References").Select
lj = Range("A" & Rows.Count).End(xlUp).Row
Sheets("MIS - Customer Invoice").Select

For i = LR To 2 Step -1
For j = lj To 2 Step -1
If InStr(1, Range("F" & i).Value, Sheets("References").Range("A" & j)) = 1 Then
Rows(i).Delete
Exit For
End If
Next j
Next I



End Sub