+ Reply to Thread
Results 1 to 3 of 3

Delete rows which do not meet specific criteria (text values)

Hybrid View

  1. #1
    Registered User
    Join Date
    07-09-2011
    Location
    Manheim
    MS-Off Ver
    Excel 2010
    Posts
    23

    Delete rows which do not meet specific criteria (text values)

    I have a really wierd issue with excel. I am trying to eliminate my longdatabase, rows are 170,000. This code doesnt work

    Sub Delete_Rows_ColB()
    
    Dim rng As Range, cell As Range, del As Range
    Dim strCellValue As String
    Set rng = Intersect(Range("D:D"), ActiveSheet.UsedRange)
    For Each cell In rng
     
    If (InStr(strCellValue, "ELF") > 0 Or InStr(strCellValue, "OLEP") > 0) Then
      If del Is Nothing Then
      Set del = cell
      Else: Set del = Union(del, cell)
      End If
    End If
    Next cell
    On Error Resume Next
    del.EntireRow.Delete
    End Sub
    while this code works fine:

    Sub Delete_Rows_ColB()
    
    Dim rng As Range, cell As Range, del As Range
    Dim strCellValue As String
    Set rng = Intersect(Range("D:D"), ActiveSheet.UsedRange)
    For Each cell In rng
     
    If InStr(strCellValue, "ELF") Or InStr(strCellValue, "OLEP") Then
      If del Is Nothing Then
      Set del = cell
      Else: Set del = Union(del, cell)
      End If
    End If
    Next cell
    On Error Resume Next
    del.EntireRow.Delete
    End Sub
    As you see, the two codes return different outcome. The first (doesnt work) keeps only the rows with ELF or OLEP and the second deletes them.

    Any help of how i can tweak my first code?

    thanks a lot

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Delete rows which do not meet specific criteria (text values)

    try
    Sub Delete_Rows_ColB()
    Dim rng As Range, r As Range, del As Range
    Dim strCellValue As String
    Set rng = Intersect(Range("D:D"), ActiveSheet.UsedRange)
    For Each r In rng
        If InStr(r.Value, "ELF") Or InStr(r.Value, "OLEP") Then
            If del Is Nothing Then
                Set del = r
            Else: Set del = Union(del, r)
            End If
        End If
    Next r
    If Not del Is Nothing Then del.EntireRow.Delete
    End Sub

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Delete rows which do not meet specific criteria (text values)

    You might find this faster than looping
    Sub DeleteRows()
        Dim LastRow As Long
        
        LastRow = Range("D" & Rows.Count).End(xlUp).Row
        Range("K2").Resize(LastRow - 1, 1).Formula = "=IF(OR(ISNUMBER(SEARCH(" & Chr(34) & "ELF" & Chr(34) & ",D2)),ISNUMBER(SEARCH(" & Chr(34) & "OLEP" & Chr(34) & ",D2,1)))," & Chr(34) & "DELETE" & Chr(34) & ","""")"
        With Columns("K:K")
            .AutoFilter Field:=1, Criteria1:="<>"
            Rows("2:" & LastRow).EntireRow.Delete
            .AutoFilter
            .Clear
        End With
    End Sub
    Change Columns("K:K") & Range("K2") to suit a spare column in your sheet.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1