+ Reply to Thread
Results 1 to 5 of 5

match entire cell content and delete row

Hybrid View

  1. #1
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91

    match entire cell content and delete row

    Hi!

    So I have a separate sheet (called "negative") with thousands of phrases I do not want in my "data" sheet. I want to filter through column C in "data" sheet for any phrases that matches entire cell content of any items in column A of the "negative" sheet.

    The filter and delete row works, except I don't know for sure if it is matching THE ENTIRE CELL CONTENT before deleting the row. It seems to be deleting when the cell contents are close to what I have on my list in my "negative" sheet.

    Thanks in advance!

    Sub Delete_with_Autofilter_More_Criteria()
        Dim rng As Range
        Dim cell As Range
        Dim CriteriaRng As Range
        Dim calcmode As Long
    
        With Application
            calcmode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
    
        With Sheets("negative")
            Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
        End With
    
        'Loop through the cells in the Criteria range
        For Each cell In CriteriaRng
    
            With Sheets("data")
    
                'Firstly, remove the AutoFilter
                .AutoFilterMode = False
    
                'Apply the filter
                .Range("C1:C" & .Rows.Count).AutoFilter Field:=1, Criteria1:=cell.Value
    
                With .AutoFilter.Range
                    Set rng = Nothing
                    On Error Resume Next
                    Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                              .SpecialCells(xlCellTypeVisible)
                    On Error GoTo 0
                    If Not rng Is Nothing Then rng.EntireRow.Delete
                End With
    
                'Remove the AutoFilter
                .AutoFilterMode = False
            End With
    
        Next cell
    
        With Application
            .ScreenUpdating = True
            .Calculation = calcmode
        End With
        
    End Sub

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: match entire cell content and delete row

    Stop the code before doing the deletion and look at the filter.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91

    Re: match entire cell content and delete row

    if i stop the code, then it will no longer serve as an automatic filter and delete code...

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: match entire cell content and delete row

    i think shg is saying, temporarily change the code so it only works up to the set autofilter, then look at it to see what is showing,just to debug it.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: match entire cell content and delete row

    Use the F8 Key to step the debugger through your code then you can see what it is doing along each step of the way... (or from the 'Debug' menu of the VBA window).

    One thing I would be tempted to do would be this:

    [code] 'Apply the filter
    .Range("C1:C" & .Rows.Count).AutoFilter Field:=1, Criteria1:="=" & cell.Value[code]

    It is also worth noting that if cell.value contains a wildcard it will be used in the filter, for example, if cell.value="A* Pass" then the filter will match entries of "A* Pass", but also;
    "A rubbish Pass"
    "A good Pass"
    "Arabian Pass"

    If you do have wildcards present in the cell.value then you might have to do some kind of escape sequence around them before using them as the input to your filter.
    If you find the response helpful please click the scales in the blue bar above and rate it
    If you don't like the response, don't bother with the scales, they are not for you

+ 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