Hello SamuelT,
There are a few tricks to speed up the process. First, use a block move to copy the cell values into an array. It is much quicker to access the array elements than the cells themselves. The next trick is create an array of rows themselves using the Union method for a Range object. We can then have the system delete our data without creating a For...Next loop. Here is the revised macro. It uses the methods I just described.
Sub RowRemover()
Dim c As Range
Dim Data As Variant
Dim DeleteRows As Range
Dim Item As Variant
Dim R As Long
Dim SrchRng
Set SrchRng = ActiveSheet.Range("E7", ActiveSheet.Range("E1000").End(xlUp))
Data = SrchRng.Value 'Block transfer cell values to an array
R = SrchRng.Row
For Each Item In Data
If Item = "NB" Then
If DeleteRows Is Nothing Then Set DeleteRows = Rows(R)
Set DeleteRows = Union(Rows(R), DeleteRows) 'Create array of rows to delete
End If
R = R + 1
Next Item
If Not DeleteRows Is Nothing Then DeleteRows.Delete
End Sub
Bookmarks