Remove entire row with criteria

Michael Noze
Michael Noze used Ask the Experts™
Hi Experts,

I'm trying to code a simple function to delete an entire and do a shift up to replace the deleted row.
when the value of a cell in column A contains "A" or "OQ" or "la" or "lq"
I've coded this, but isn't working properly.

Is there a solution to this?

Thank you

Sub DeleteRow()

Dim SrchRng As Range, cel As Range
Application.ScreenUpdating = False
Set SrchRng = Range("A:A")

For Each cel In SrchRng
    If Not InStr(1, cel.Value, "A") > 0 Then
        Worksheets("Feuille1").Rows(cel.Row).Delete Shift:=xlShiftUp
    End If
Next cel
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
It is worth noting that InStr is case insensitive the way you were using it. The following tweak to your code is working for me. I restricted it to the used range of the worksheet because there is no need to waste time iterating through all the blank rows below your data.
Sub DeleteRow()

Dim SrchRng As Range, cel As Range
Application.ScreenUpdating = False

Set SrchRng = Worksheets("Feuille1").Range("A:A")
Set SrchRng = Intersect(SrchRng, SrchRng.Worksheet.UsedRange)

For Each cel In SrchRng.Cells
    If Not InStr(1, cel.Value, "A") > 0 Then
        cel.EntireRow.Delete
    ElseIf Not InStr(1, cel.Value, "OQ") > 0 Then
        cel.EntireRow.Delete
    ElseIf Not InStr(1, cel.Value, "la") > 0 Then
        cel.EntireRow.Delete
    ElseIf Not InStr(1, cel.Value, "lq") > 0 Then
        cel.EntireRow.Delete
    End If
Next cel


End Sub

Open in new window

Michael Noze

Author

Commented:
Thanks! It works perfectly!
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I am bothered by the "Not" in your original code.

If your intent is to delete the row if column contains either A, OQ, la or lq then I believe this code does the needful:
Sub DeleteRow()

Dim SrchRng As Range, cel As Range
Dim s As String
Dim i As Long, n As Long
Dim v As Variant
Application.ScreenUpdating = False

Set SrchRng = Worksheets("Feuille1").Range("A:A")
Set SrchRng = Intersect(SrchRng, SrchRng.Worksheet.UsedRange)
n = SrchRng.Rows.Count
v = SrchRng.Value

For i = n To 1 Step -1
    s = v(i, 1)
    If InStr(1, s, "A") > 0 Then
        SrchRng.Rows(i).EntireRow.Delete
    ElseIf InStr(1, s, "OQ") > 0 Then
        SrchRng.Rows(i).EntireRow.Delete
    ElseIf InStr(1, s, "la") > 0 Then
        SrchRng.Rows(i).EntireRow.Delete
    ElseIf InStr(1, s, "lq") > 0 Then
        SrchRng.Rows(i).EntireRow.Delete
    End If
Next

End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial