Results 1 to 2 of 2

Thread: delete rows not matching certain criteria

  1. #1

    Unhappy delete rows not matching certain criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Is there anyway way to delete those rows not matching certain criteria ?
    For example, required rows in Column A - always have either xxxxx-x formatted Account# or Date or word begins with "Total :" anything other then this- those rows are not useful and i want to automatically delete all those unwanted rows.
    Delete the the rows which has value other then xxxxx-x formatted account# or date or word "Total :"
    [Account# could be xxxxx-x or xxxx-x or xxx-x or xx-x or x-x means 5digit-1digit or 4digit-1digit or 3digit-1digit or 2digit-1digit or 1digit-1digit
    Data is huge so cannot do by auto filter. Need Macro.
    Thanx a lot

    ====INPUT====
    Column A
    56981-3
    1459-3
    10/25/2010
    69655-9

    Apple
    Orange
    Total:
    01/02/2009

    Paint1
    987-1
    Yellow
    Total:

    ====OUTPUT===
    Column A
    56981-3
    1459-3
    10/25/2010
    69655-9

    Total:
    01/02/2009

    987-1
    Total:

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14
    Quote Originally Posted by sanketgroup View Post
    Data is huge so cannot do by auto filter.
    I'm not sure why huge data would prevent you from using Autofilter, unless you're getting an error because you have to many non-contiguous cells. That's easy to fix though:
    • Run a formula down the first blank column: =A1+1
    • Copy the column then right click and choose PasteSpecial-->Values
    This will give you a nice list of your original data source order. Now:
    • Sort by the fields you want to use to cull data (or even run another formula down the side to determine what needs nuking)
    • Filter and delete
    • Un-filter and sort back into the original source order
    • Delete the original source order column you made.
    If you still want to go with a macro, then we can do this, but to preserve speed, this is exactly what I'd code the macro to do. (Data that large in a loop will take a long time.)

    Again, if you do want to go that route, please upload a sample workbook that has all forms of data you wish to work with in it so that we can test solutions against it. (Click the Go Advanced button on the post to do this.)

    Cheers,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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