Advance Search and GoTo a cell that meets a certain search criteria

Morning All,

Well I am kind of stumped. Pretty much im building a macro that will sort/delete unnecessary rows from a sheet. I work with a "ANCIENT" / OLD database system that pulls records in single rows and very cluttered, so what I do (MANUALLY) is I filter the rows from A to Z, delete about the first 20,000 rows, and the last about 20,000 rows then do a text to column to sort the different information. I say "about" because it ranges everytime I pull from the database we use.

Here is the problem, because the amount of rows above and bellow ranges; the only way to identify where the first row should start is by doing a criteria test. In excel I can use this;

Please Login or Register  to view this content.

As you probably figured by now, I deal with CC account numbers which is why I can't upload the workbook.

Here is what I have so far that attempts to search for the criteria, then delete if it doesnt match;

Please Login or Register  to view this content.

This code kind of works.... Only problem is sometimes I work with sheets that have 60,000 rows and for that macro to run would take hours! How do I have the macro search and find the first instance of that criteria of an account number and delete all rows above that, then find the last instance of that criteria and delete all the rows below that?

Any advice is greatly appreciated! Thank you

Hi

This type of problem is quite common and I see it all the time. The approach I adopt almost invariably is as follows.

1. Do the Text to Columns first.
2. Give yourself some working room by adding three or four helper columns A-C
3. Now work out some helper formulae which identify whether a particular row should be deleted or not. The aim being to eventually identify just the rows you don't want e.g. in A1

Please Login or Register  to view this content.

depending on your data it may be necessary to build this up with AND functions which examine other columns, and if it starts to get complicated just use the other helper columns to break the testing down. Once you've got the formula right, copy it down your whole list and filter column A for the word "Delete" and then just select and delete the filtered rows.

HTH

If you're examining column A and deleting all the rows that DON'T have a number in column A, but have text of somekind instead, try this:

Please Login or Register  to view this content.

If there are blanks to deal with, too:

Please Login or Register  to view this content.