Looking for suggestion on how to rewrite a loop to make faster?

[mayoung]

I have created the following loop but it takes to long to run. Any suggestions on how to change it? I would be very grateful. New at this and self taught.

    Range("F2").Select

    Do While Not IsEmpty(ActiveCell.Offset(0, -5))


        If Selection = "FIELD SERVICE" Then


            With Selection
                .EntireRow.Delete
            End With


                    ElseIf ActiveCell.Interior.Color = 10066431 Then
            
                        With Selection
                            .EntireRow.Delete
                        End With


        ElseIf Selection = "INTERNAL" Then


            With Selection
                .EntireRow.Delete
            End With


        ElseIf ActiveCell.Select <> "FIELD SERVICE" Then


            ActiveCell.Offset(1, 0).Select


        End If


    Loop

 

[My Aswer Is This]

Tell me in words what your wanting to do.

Sounds like your looking for values in Column A for:

FIELD SERVICE

INTERNAL

Or

Interior.Color = 10066431

And if found delete entire row.

Is this what your trying to do?​

 

[mayoung]

I am wanting to check cells in column F and loop down as long as column A is not empty
and delete entire row if the criteria you have listed is found. The main goal is delete the entire row if any cell in column F contains the criteria you have listed.

 

[Rick Rothstein]

I have created the following loop but it takes to long to run. Any suggestions on how to change it?

It will be much faster if you do not use a loop at all...

[table="width: 500"]
[tr]
	[td]Sub DeleteRows()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp)).Offset(, 5)
    .Replace "FIELD SERVICE", "#N/A", xlWhole, , False, , False, False
    .Replace "INTERNAL", "#N/A", xlWhole, , False, , False, False
    Application.FindFormat.Clear
    Application.FindFormat.Interior.Color = 10066431
    .Replace "", "#N/A", , , , , True, False
    Application.FindFormat.Clear
    Columns("F").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub[/td]
[/tr]
[/table]

 

[Michael M]

I like the use of the Replace / xlerrors Rick.
Very slick...

 

[footoo]

Just for interest, could also use a filter :

Sub DeleteRows()
Dim rw&
Application.ScreenUpdating = False
Rows(1).Insert
[F1] = "Temp"
rw = Cells(Rows.Count, "A").End(xlUp)(2).Row
Cells(rw, "F").Interior.Color = 10066431
[F:F].AutoFilter
With Range("F1:F" & rw)
    .AutoFilter Field:=1, Criteria1:=10066431, Operator:=xlFilterCellColor
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter Field:=1, Criteria1:="=FIELD SERVICE", Operator:=xlOr, Criteria2:="=INTERNAL"
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub

 

[mayoung]

Rick Rothstein I am a self taught person and still learning everyday. Can you please explain two pieces of your code so I can better understand how it works? The .Repace Field Service and the part about the color? Trying to understand the #NA and xlwhole and the commas with true false etc? Thank you in advance for taking the to explain if you do.

 

[mayoung]

Footoo thank you for your response as well. Both codes work great.