Results 1 to 4 of 4

how to delete rows not containing the criteria

Threaded View

sasswe how to delete rows not... 02-05-2008, 07:11 PM
sasswe any help?:confused: 02-06-2008, 10:42 PM
stevekirk hi sasswe, i think you... 02-06-2008, 11:06 PM
sasswe basically i have a speadsheet... 02-07-2008, 02:11 AM
  1. #1
    Registered User
    Join Date
    02-05-2008
    Posts
    8

    how to delete rows not containing the criteria

    how can i delete rows not containing the criteria, there are a few i\on the net but they all delete rows containing criteria. how can i do the reverse. it also needs to delete rows even on a partial match.

    the code beloew is the one i use to delete rows.


    Option Explicit
     
    Sub KillRows()
         
        Dim MyRange As Range, DelRange As Range, C As Range
        Dim MatchString As String, SearchColumn As String, ActiveColumn As String
        Dim FirstAddress As String, NullCheck As String
        Dim AC
         
         'Extract active column as text
        AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
        ActiveColumn = AC(0)
         
        SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn)
         
        On Error Resume Next
        Set MyRange = Columns(SearchColumn)
        On Error GoTo 0
         
         'If an invalid range is entered then exit
        If MyRange Is Nothing Then Exit Sub
         
        MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value)
        If MatchString = "" Then
            NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _
            "Type Yes to do so, else code will exit", "Caution", "No")
            If NullCheck <> "Yes" Then Exit Sub
        End If
         
        Application.ScreenUpdating = False
         
         'to match the WHOLE text string
        'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole)
         'to match a PARTIAL text string use this line
         Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlPart)
         'to match the case and of a WHOLE text string
         'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)
         
        If Not C Is Nothing Then
            Set DelRange = C
            FirstAddress = C.Address
            Do
                Set C = MyRange.FindNext(C)
                Set DelRange = Union(DelRange, C)
            Loop While FirstAddress <> C.Address
        End If
         
         'If there are valid matches then delete the rows
        If Not DelRange Is Nothing Then DelRange.EntireRow.Delete
         
        Application.ScreenUpdating = True
         
    End Sub
    Last edited by VBA Noob; 02-05-2008 at 07:15 PM.

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