Code to rows does not delete every row?

[Prevost]

Hi There. I have this code below which is deleting rows that contain a certain value in the corresponding C column. (I am cleaning up bills of materials that have these redundant lines in them). I have written code like this before to delete lines and it works, but it seems to miss some lines and I have to run it several times to ensure all the rows are deleted. Is there something in the code that is causing this that I am missing (which I think is more likely the case) or has anyone else come across this? Thanks!

Sub RemoveLines()
    
    Dim MyRange As Range, Cell As Range
    Dim Search(1 To 10) As String
    Dim i As Integer
    
        Set MyRange = Range("C2:C91953")
    
        Search(1) = "SPARE LINE"
        Search(2) = "RAWLBS1"
        Search(3) = "RAWLBS2"
        Search(4) = "RAWLBS3"
        Search(5) = "RAWLBS4"
        Search(6) = "RAWFT"
        Search(7) = "MISC"
        
            For Each Cell In MyRange
                For i = 1 To 7
                    If Cell = Search(i) Then
                        Cell.EntireRow.Delete
                        Exit For
                    End If
                Next i
            Next Cell
    
End Sub

 

[RoryA]

You need to loop backwards through the range:

Sub RemoveLines()
    
    Dim MyRange As Range, Cell As Range
    Dim Search(1 To 10) As String
    Dim i As Integer
    Dim n as long
    
        Set MyRange = Range("C2:C91953")
    
        Search(1) = "SPARE LINE"
        Search(2) = "RAWLBS1"
        Search(3) = "RAWLBS2"
        Search(4) = "RAWLBS3"
        Search(5) = "RAWLBS4"
        Search(6) = "RAWFT"
        Search(7) = "MISC"
        
            For n = MyRange.Cells.Count to 1 Step -1
                For i = 1 To 7
                    If MyRange.Cells(n, 1) = Search(i) Then
                        MyRange.Cells(n, 1).EntireRow.Delete
                        Exit For
                    End If
                Next i
            Next n
    
End Sub

 

[Prevost]

Thanks RoryA. Can you explain why please? Is it because if you have consecutive cells that contain the specific string, the top row will be deleted and then the row below it (which also should be deleted) moves up one and takes the previous row's place. Then Excel thinks that the row has already been deleted and goes to the next cell down, therefore leaving a row there which should have been deleted...

 

[RoryA]

That's exactly right.

 

[hateme28]

this is another way of doing what you need.. It may be a little faster because it searches for the keyword instead of checking every single cell.

Sub removelines()
Application.ScreenUpdating = False
Dim MyRange As Range, i As Integer, Search(1 To 10) As String
    Set MyRange = Range("C2:C91953")
    Search(1) = "SPARE LINE"
    Search(2) = "RAWLBS1"
    Search(3) = "RAWLBS2"
    Search(4) = "RAWLBS3"
    Search(5) = "RAWLBS4"
    Search(6) = "RAWFT"
    Search(7) = "MISC"
For i = 1 To 7
    Do
    Set c = MyRange.Find(Search(i))
        If Not c Is Nothing Then
            Cells(c.Row, c.Column).EntireRow.Delete
        End If
    Loop Until c Is Nothing
Next
Application.ScreenUpdating = True
End Sub

 

[Prevost]

Thanks for the code. I am always looking to improve my VBA!