Deleting rows with specific data

[JerBear]

Hello! I have several hundred lines of data on one worksheet, where I'm looking for "IMP" in column A and deleting the row with "IMP" in it. The word "IMP" can appear on any row within the column. I have two issues that I can't figure out. The first is, how do I select column A without setting a range of rows? The second is, when this code runs I receive an error "Type mismatch" after the last "IMP" line is deleted.

Thanks in advance!

Book2.xlsm

Sub DeleteCells1()
    Dim rng As Range
    Dim i As Integer, counter As Integer
    Set rng = Range("A5:A165")
    i = 1
    For counter = 1 To rng.Rows.Count
        If rng.Cells(i) = "IMP" Then
            rng.Cells(i).EntireRow.Delete
        Else
            i = i + 1
        End If
    Next
End Sub

[Mumps1]

Try this code. It will automatically adjust for the number of cells in A that have data.

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim x As Long
    On Error Resume Next
    For x = bottomA To 1 Step -1
        If Cells(x, "A") = "IMP" Then
            Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

[JerBear]

Thanks Mumps1! But I get a "Type Mismatch" error for "If Cells(x,"A") = "IMP" Then"

[JOHN H. DAVIS]

Another option:

Sub JerBear()
Application.Screenupdating = False
Range("A4:A165").Autofilter 1, "IMP"
Range("A5:A165").Specialcells(xlCellTypeVisible).Entirerow.Delete xlup
Activesheet.UsedRange.Autofiltermode = False
Application.Screenupdating = True
End Sub

[Mumps1]

Sorry, JerBear. I noticed the sam thing and corrected the code. Try:

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim x As Long
    On Error Resume Next
    For x = bottomA To 1 Step -1
        If Cells(x, "A") = "IMP" Then
            Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

[JerBear]

Mumps1 - with the updated code, any lines below "IMP" get deleted as well.

John - I get an error: "Object doesn't support this property or method" for line "Activesheet.UsedRange.AutoFilterMode = False."

[JOHN H. DAVIS]

Mumps1 - with the updated code, any lines below "IMP" get deleted as well.

John - I get an error: "Object doesn't support this property or method" for line "Activesheet.UsedRange.AutoFilterMode = False."

Oops

Sub JerBear()
Application.Screenupdating = False
Range("A4:A165").Autofilter 1, "IMP"
Range("A5:A165").Specialcells(xlCellTypeVisible).Entirerow.Delete xlup
Activesheet.Autofiltermode = False
Application.Screenupdating = True
End Sub

[JerBear]

JHD - interesting concept - I never thought to use the Filter! Thank you - your code worked just fine. I appreciate your time to help me!

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.

[JerBear]

Mumps1 - Thank you for your time - I can use the code you provided in another part of my workbook!

[Mumps1]

I'm glad everything worked out.