Macro to delete row if doesn't contain certain text

S

srr797

New Member
Joined
Nov 6, 2010
Messages
39
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!
 
Sort by date Sort by votes
John Davis said:
Here's another approach:

Code: 
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
Click to expand...


Thanks... this method works, it just takes quite a while... is there any way to make it run quicker?

Thanks again!
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Trebor76 said:
That's odd - it worked fine for me :confused:

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.
Click to expand...


Robert, after reviewing your question; yes there is one space after the entry. hope that helps.

Thanks for your time!
 
Upvote 0
srr797 said:
Thanks... this method works, it just takes quite a while... is there any way to make it run quicker?

Thanks again!
Click to expand...

You could turn the screenupdating off. Don't know if it will make that much of a difference:

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

Application.Screenupdating = False

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

Application.Screenupdating = True

End Sub
 
Upvote 0
Trebor76 said:
For that size, try this:

Code: 
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
Click to expand...


I got this to work! Just added a space after "IP-0000063409" in the code

Have one more question though... how can I modify this to add in other variable. For example if I wanted this to not delete IP-0000063408 and IP-0000063407
 
Upvote 0
Hi srr797,

Try this (initially on a copy of your data in case the results are not as expected) which automatically removes any trailing spaces and uses two criteria. Note that even on large datasets this code will be quite fast as it deletes all the required rows in one go as opposed to a row at a time:

Code: 
Sub Macro2()
        
    '//Declare variables//
    Dim varDelItem1 As Variant, _
        varDelItem2 As Variant
    Dim lngRowStart As Long, _
        lngRowLast As Long, _
        lngRowActive As Long
    Dim strMyCol As String
    Dim rngDelRange As Range
    
    '//Set variables//
    varDelItem1 = "IP-0000063407"
    varDelItem2 = "IP-0000063408"
    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 Trim(Cells(lngRowActive, strMyCol)) <> varDelItem1 And _
           Trim(Cells(lngRowActive, strMyCol)) <> varDelItem2 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 applicable row in Column " & strMyCol & " matched """ & varDelItem1 & """" & " and """ & varDelItem2 & """.", vbExclamation, "Delete Row Editor"
    End If
    
    Application.ScreenUpdating = True
        
End Sub

HTH

Robert
 
Last edited:
Upvote 0
Trebor76 said:
Hi srr797,

Try this (initially on a copy of your data in case the results are not as expected) which automatically removes any trailing spaces and uses two criteria. Note that even on large datasets this code will be quite fast as it deletes all the required rows in one go as opposed to a row at a time:

Code: 
Sub Macro2()
        
    '//Declare variables//
    Dim varDelItem1 As Variant, _
        varDelItem2 As Variant
    Dim lngRowStart As Long, _
        lngRowLast As Long, _
        lngRowActive As Long
    Dim strMyCol As String
    Dim rngDelRange As Range
    
    '//Set variables//
    varDelItem1 = "IP-0000063407"
    varDelItem2 = "IP-0000063408"
    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 Trim(Cells(lngRowActive, strMyCol)) <> varDelItem1 And _
           Trim(Cells(lngRowActive, strMyCol)) <> varDelItem2 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 applicable row in Column " & strMyCol & " matched """ & varDelItem1 & """" & " and """ & varDelItem2 & """.", vbExclamation, "Delete Row Editor"
    End If
    
    Application.ScreenUpdating = True
        
End Sub
HTH

Robert
Click to expand...


Works perfectly! Thanks so much for your time and help! It also works much faster than some of the other codes I received. Thanks again
 
Upvote 0
Works perfectly! Thanks so much for your time and help! It also works much faster than some of the other codes I received. Thanks again
Click to expand...

You're welcome :)
 
Upvote 0
Is there an alternative which also works on a sheet containing many rows ( 200.000 > rows > 100.000 )? I tried the code from Trebor76 but excel won't respond after applying the macro.
 
Upvote 0
Thanks, Trebor!

Is there a way to include a larger number of variables? I took your example above and added 6 more variables trying check column AB in my file and am getting a Run-Time error '13': Type mismatch at the "If Trim(Cells..." area.

Thoughts?

Code: 
Sub Macro2()
        
    '//Declare variables//
    Dim varDelItem1 As Variant, _
        varDelItem2 As Variant, _
        varDelItem3 As Variant, _
        varDelItem4 As Variant, _
        varDelItem5 As Variant, _
        varDelItem6 As Variant, _
        varDelItem7 As Variant, _
        varDelItem8 As Variant
    Dim lngRowStart As Long, _
        lngRowLast As Long, _
        lngRowActive As Long
    Dim strMyCol As String
    Dim rngDelRange As Range
    
    '//Set variables//
    varDelItem1 = "Level 1"
    varDelItem2 = "Level 2"
    varDelItem3 = "Level 3"
    varDelItem4 = "Level 4"
    varDelItem5 = "Level 5"
    varDelItem6 = "Level 6"
    varDelItem7 = "Level 7"
    varDelItem8 = "Level 8"
    lngRowStart = 2 'Initial data row.  Change to suit.
    strMyCol = "AB" 'Column containing relevant data.  Change to suit.
    lngRowLast = Cells(Rows.Count, strMyCol).End(xlUp).Row
    
    Application.ScreenUpdating = False
        
    For lngRowActive = lngRowStart To lngRowLast
        If Trim(Cells(lngRowActive, strMyCol)) <> varDelItem1 And _
           Trim(Cells(lngRowActive, strMyCol)) <> varDelItem2 And _
           Trim(Cells(lngRowActive, strMyCol)) <> varDelItem3 And _
           Trim(Cells(lngRowActive, strMyCol)) <> varDelItem4 And _
           Trim(Cells(lngRowActive, strMyCol)) <> varDelItem5 And _
           Trim(Cells(lngRowActive, strMyCol)) <> varDelItem6 And _
           Trim(Cells(lngRowActive, strMyCol)) <> varDelItem7 And _
           Trim(Cells(lngRowActive, strMyCol)) <> varDelItem8 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 applicable row in Column " & strMyCol & " matched """ & varDelItem1 & """" & " and """ & varDelItem2 & """.", vbExclamation, "Delete Row Editor"
    End If
    
    Application.ScreenUpdating = True
        
End Sub
 
Upvote 0
Hi iufreak,

Welcome to MrExcel!!

I'd say there's a formula or reference error (i.e. #DIV/0 or #REF!) in the range.

Assuming that is the case, this will do the job:

Code: 
Option Explicit
Sub Macro2()
        
    '//Declare variables//
    Dim varDelItem1 As Variant, _
        varDelItem2 As Variant, _
        varDelItem3 As Variant, _
        varDelItem4 As Variant, _
        varDelItem5 As Variant, _
        varDelItem6 As Variant, _
        varDelItem7 As Variant, _
        varDelItem8 As Variant
    Dim lngRowStart As Long, _
        lngRowLast As Long, _
        lngRowActive As Long
    Dim strMyCol As String
    Dim rngDelRange As Range
    
    '//Set variables//
    varDelItem1 = "Level 1"
    varDelItem2 = "Level 2"
    varDelItem3 = "Level 3"
    varDelItem4 = "Level 4"
    varDelItem5 = "Level 5"
    varDelItem6 = "Level 6"
    varDelItem7 = "Level 7"
    varDelItem8 = "Level 8"
    lngRowStart = 2 'Initial data row.  Change to suit.
    strMyCol = "AB" 'Column containing relevant data.  Change to suit.
    lngRowLast = Cells(Rows.Count, strMyCol).End(xlUp).Row
    
    Application.ScreenUpdating = False
        
    For lngRowActive = lngRowStart To lngRowLast
        If IsError(Cells(lngRowActive, strMyCol)) = True 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
        Else
           If Trim(Cells(lngRowActive, strMyCol)) <> varDelItem1 And _
              Trim(Cells(lngRowActive, strMyCol)) <> varDelItem2 And _
              Trim(Cells(lngRowActive, strMyCol)) <> varDelItem3 And _
              Trim(Cells(lngRowActive, strMyCol)) <> varDelItem4 And _
              Trim(Cells(lngRowActive, strMyCol)) <> varDelItem5 And _
              Trim(Cells(lngRowActive, strMyCol)) <> varDelItem6 And _
              Trim(Cells(lngRowActive, strMyCol)) <> varDelItem7 And _
              Trim(Cells(lngRowActive, strMyCol)) <> varDelItem8 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
        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.
        MsgBox "No rows were deleted.", vbExclamation, "Delete Row Editor"
    End If
    
    Application.ScreenUpdating = True
        
End Sub

Regards,

Robert
 
Upvote 0
You must log in or register to reply here.

Similar threads

turkanet
  • Question
FILTER OR DELETE ROWS CONTAINING WORDS STARTING AND ENDING WITH SPECIFIC TEXT USING VBA
Replies
2
Views
139
mse330
M
J
  • Question
How to delete row on two worksheets
Replies
2
Views
313
jdcar
J
M
  • Question
VBA to delete the rows which contains the data of previous week!!
Replies
7
Views
251
Alex Blakenburg
A
L
  • Question
Return text from cell in column containing text, as long as values in other columns match several criteria
Replies
7
Views
384
etaf
etaf
D
  • Question
FILTER Function Doesn't Display Formatting
Replies
3
Views
78
Fluff
Fluff

Forum statistics

Threads
1,214,918
Messages
6,122,255
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top