Delete row if range does not contain string

[Shadkng]

Hi, can someone modify the code below to delete the entire row instead of just the cell where the text does not begin with the word "PARTS". I found this code online so If there is a better way to do this please offer. Thanks in advance.

Sub testparts()
For Each cl In Range("A44:A47")
    If UCase(Left(cl.Value, 5)) <> "PARTS" Then
        cl.ClearContents
    End If
Next cl
End Sub

 

[Rick Rothstein]

Do you want to physically delete the row or do what the macro you posted does and simply clear the cells' contents?

 

[Shadkng]

Do you want to physically delete the row or do what the macro you posted does and simply clear the cells' contents?

Deleting the row would be preferable.

 

[Rick Rothstein]

Give this a try (change the range in the With statement to match the actual range of cells you want to process)...

Sub DeleteNonPARTS()
  With Range("A44:A47")
    .Value = Evaluate(Replace("IF(LEFT(@,5)<>""PARTS"",""#N/A"",IF(@="""","""",@))", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[Shadkng]

Give this a try (change the range in the With statement to match the actual range of cells you want to process)...

Sub DeleteNonPARTS()
  With Range("A44:A47")
    .Value = Evaluate(Replace("IF(LEFT(@,5)<>""PARTS"",""#N/A"",IF(@="""","""",@))", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

Looks good! Thanks so much.

 

[smozgur]

Looks good! Thanks so much.

The marked solution has been changed accordingly.

@Shadkng - in your future questions, please mark the post that answered your question instead of your final post as it will help future readers.