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
This will give you a nice list of your original data source order. Now:
- Run a formula down the first blank column: =A1+1
- Copy the column then right click and choose PasteSpecial-->Values
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.)
- 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.
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.)
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.