+ Reply to Thread
Results 1 to 6 of 6

Macro to filter data and delete a row if a specific value exists

  1. #1
    Registered User
    Join Date
    05-11-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    20

    Macro to filter data and delete a row if a specific value exists

    I have a huge spreadsheet that I need to slim down to only the data that I need. Column G is one of several different options (ABI, CCK, DEP, TSL, TSS, WTD or XFR). I only need to look at the data that contains CCK or DEP in column G. I found the macro below and tweaked it for my spreadsheet, however, when I run it...not everything is deleted out like it should be. It removes all of the columns with TSL, TSS and XFR, like it is supposed to, but it still leaves in the rows with ABI and WTD in column G. Any ideas?



    Sub Delete_with_Autofilter_Array()
    Dim rng As Range
    Dim calcmode As Long
    Dim myArr As Variant
    Dim I As Long
    With Application
    calcmode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With
    myArr = Array("ABI", "TSL", "TSS", "WTD", "XFR")
    For I = LBound(myArr) To UBound(myArr)
    With ActiveSheet
    .AutoFilterMode = False
    .Range("G1:G" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)
    Set rng = Nothing
    With .AutoFilter.Range
    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
    .AutoFilterMode = False
    End With
    Next I
    With Application
    .ScreenUpdating = True
    .Calculation = calcmode
    End With
    End Sub

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,456

    Re: Macro to filter data and delete a row if a specific value exists

    Hi coxb,

    Though this doesn't use a filter, it's still extremely fast:

    Please Login or Register  to view this content.
    HTH

    Robert

    PS You need to wrap your code with tags as I've done.
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    05-11-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Macro to filter data and delete a row if a specific value exists

    I tried the code you suggested, but it deletes every row and leaves me with a blank worksheet. I stepped through and looks like it is happening when it gets to this part:

    On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0 'Turn error reporting back on

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,456

    Re: Macro to filter data and delete a row if a specific value exists

    That's odd as it worked for me

    Are you absolutely sure there's CCK or DEP entries from cell G1 down column G? Note even a space after these will result in the macro deleting that row as these don't match the desired text.

    Make sure you run the code on copies of your data until the code works as expected.

    Robert

  5. #5
    Registered User
    Join Date
    05-11-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Macro to filter data and delete a row if a specific value exists

    I verified all my data is in (without any spaces) and I tried it again with a small sampling of my data and it seems to work fine. Maybe it has something to do with the size of the file I"m working in and the number of rows it needs to delete out? Not sure why that would cause it to delete everything though. I even tried my original macro on the small sample and it works...just when all of the data is there, it leave in ABI and WTD (which both have over 35000 rows each).

    The file I'm working on came in with 248987 rows of data.
    35519 of those rows were ABI in col G (which need to be deleted)
    985 were CCK (which should stay)
    35892 were DEP (which should stay)
    2 were TSL (which need to be deleted)
    905 were TSS (which need to be deleted)
    174533 were WTD (which need to be deleted)
    1151 were XFR (which need to be deleted)

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,456

    Re: Macro to filter data and delete a row if a specific value exists

    Maybe it has something to do with the size of the file I"m working in and the number of rows it needs to delete out?
    No, I can't see that being an issue.

    From the codes you listed, you could simply sort the entire data by column G, find the first TSL entry (row) and then delete from that row to the last row (SHIFT + END + DOWN ARROW)

    HTH

    Robert

+ 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