Macro to delete row if doesn't contain certain text

[srr797]

Hello,

I am in need of what seems to be a fairly simple macro, which will delete (and shift up) any row that does not contain the text "IP-0000063409" in column E.


Thanks very much!

 

[Mtyler]

Can't you try filtering the data in column E to only show your required text, and then copying remaining data to a clean sheet? That way it retains existing info for clarity.

 

[srr797]

Don't need the existing info... would like a macro so it would be automated every time the list is regenerated.

 

[Trebor76]

How many rows (roughly) are in Col E?

 

[srr797]

3500

 

[Trebor76]

For that size, try this:

Sub Macro2()
        
    '//Declare variables//
    Dim varDelItem As Variant
    Dim lngRowStart As Long, _
        lngRowLast As Long, _
        lngRowActive As Long
    Dim strMyCol As String
    Dim rngDelRange As Range
    
    '//Set variables//
    varDelItem = "IP-0000063409"
    lngRowStart = 2 'Initial data row.  Change to suit.
    strMyCol = "E" 'Column containing relevant data.  Change to suit.
    lngRowLast = Cells(Rows.Count, strMyCol).End(xlUp).Row
    
    Application.ScreenUpdating = False
        
    For lngRowActive = lngRowStart To lngRowLast
        If Cells(lngRowActive, strMyCol) <> varDelItem Then
            'Cater for initial setting of 'rngDelRange' range
            If rngDelRange Is Nothing Then
                Set rngDelRange = Cells(lngRowActive, strMyCol)
            Else
                Set rngDelRange = Union(rngDelRange, Cells(lngRowActive, strMyCol))
            End If
        End If
    Next lngRowActive
        
    'If the 'rngDelRange' range has been set (i.e. has something in it), then...
    If Not rngDelRange Is Nothing Then
        '...delete the rows within it.
        rngDelRange.EntireRow.Delete xlShiftUp
    'Else...
    Else
        '...inform the user that no rows are to be deleted as there was no _
        matching criteria in the dataset.
        MsgBox "No rows were deleted as every Row in Column " & strMyCol & " matched """ & varDelItem & """.", vbExclamation, "Delete Row Editor"
    End If
    
    Application.ScreenUpdating = True
        
End Sub

Robert

 

[srr797]

Thanks Robert. There seems to be a problem... when I run the macro it deletes all my data but the first row.

 

[teachmehow2macro]

Hi,

try this

code:
Sub DELROWs()
Dim couNter As Long
Dim RowCount As Long
Application.ScreenUpdating = False
RowCount = Range("E65536").End(xlUp).Row
couNter = 1
Do Until couNter > RowCount
If Range("E" & couNter).Value = "IP-0000063409" Then
Range("E" & couNter).EntireRow.Delete
RowCount = RowCount - 1
couNter = couNter - 1
End If
couNter = couNter + 1
Loop
Application.ScreenUpdating = True
End Sub

 

[Trebor76]

Thanks Robert. There seems to be a problem... when I run the macro it deletes all my data but the first row.

That's odd - it worked fine for me

Are there any trailing spaces either before and/or after each entry in Col E? If you also want to include Row 1, simply change the 'lngRowStart' variable.

 

[John Davis]

Hello,

I am in need of what seems to be a fairly simple macro, which will delete (and shift up) any row that does not contain the text "IP-0000063409" in column E.


Thanks very much!

Here's another approach:

Sub srr797()
Dim lr As Long
Dim i As Long

lr = Cells(Rows.Count, 5).End(xlUp).Row

    For i = lr To 2 Step -1
    
        If Range("E" & i) <> "IP-0000063409" Then
        
            Range("E" & i).EntireRow.Delete
            
        End If
    
    Next i

End Sub