Delete Rows based on Criteria

**amandaca** · 07-18-2011, 07:14 PM

I have a huge spreadsheet importing data from a text file (~60,000 rows of data). It is a lot of information that is recorded in real time and I need to delete rows that have erroneous values (negative, below or above a certain threshold). I need a quick way to process a large number of rows and delete all of the rows that I don't want based on the entered criteria.

Here is the VBA I have so far just for one criteria, but it's incredibly slow for a lot of rows already.

Please Login or Register  to view this content.

I attached an example spreadsheet. To explain the example...I need the VBA to check if the value of A30 is less than B16 or greater than C16, and if so, delete that row. Then move to the next criteria--if B30 is less than B17 or greater than C17, delete that row, etc. through all of the criteria. Then I need it to do the same thing for every row, leaving only the rows that meet all of the criteria.

**Armando Montes** · 07-19-2011, 12:18 AM

Try Autofilter:
http://www.rondebruin.nl/delete.htm

**amandaca** · 07-19-2011, 07:22 AM

That only gives me a filter for specific values, and it doesn't specify in which column it's looking for the value. Autofilter seems like a good method for a large number of rows but that code has a "DeleteValue" that is set to a specific value. I'm not that great with programming and can't figure out how to include all of the ranges I need and to make it look for each value only within a single column,

**amandaca** · 07-19-2011, 08:20 AM

Please Login or Register  to view this content.

This is what I've come up with so far, but it still takes at least 10 seconds to run it for just 1 set of criteria and I'm going to run the same thing for about 10 different categories. Any other ideas for a more efficient loop?

**arthurbr** · 07-19-2011, 08:24 AM

Hi and welcome to the board

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**snb** · 07-19-2011, 08:56 AM

Please Login or Register  to view this content.

**amandaca** · 07-19-2011, 09:40 AM

I'm not entirely sure how your VBA works. It seems like it's deleting random values from random columns and rows--not following the criteria given by the user-input data table, and not deleting entire rows--it leaves random values. Also, I get a Run-time error 424 - object required.

Any other suggestions/corrections to snb's code?

It seems to work fast but I have no idea what it's doing.