VBA - Delete Rows

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
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"
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Code:
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

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top