delete rows not matching certain criteria

sanketgroup

New member
Joined
Oct 8, 2011
Messages
2
Reaction score
0
Points
0
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:
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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,
 
Top