Filter one column with multiple criteria

I have a spread shee that is dynamic, it changes every week and the number of records change aswell. I want to autofilter a specific coulmn using multiple criteria. these are run in a single macro, not as a seprate sub. I am very new to VBA coding, so please be specific in explination, Thank you in advance.

My code is:

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

I then want to Delete all of them, I have read uch about this but I seem to be very unsuccesfull in exacuting it. This is the best I got at this attempt:

Please Login or Register  to view this content.

I attached a dumbed down sample of the spreadsheet I am working with. I need to delete all rows that have a status of 70, 80, or 90.




Additional FYI- I am using Excel 2007
thanks

Hello CBG05QB,

Welcome to the Forum!

There are several approaches that can be taken when deleting data. The macro below is probably the simplest of all methods.

The UsedRange property of the worksheet returns a rectangular range of cells on the worksheet. This range includes all cells with formulas, values, and formatting. Since your range is dynamic, this property will return only the cells that worksheet has now.

The cells of interest are in column "H" or 8 on the worksheet. The macro will loop through these cells one at a time and compare them to multiple criteria values of 70, 80, and 90. Whenever a match is made, the entire row is deleted and the worksheet data is shifted from the bottom up one row. You will see the For..Next loop counts from the last row to the first to match the movement of the data.

The Application.ScreenUpdating is turned off while the macro is running and turned back on when it stops. This prevents the screen from flickering while the rows are deleted and the data is shifted. This also speeds up the macro.

Please Login or Register  to view this content.

The reason your attempt is failing is that the autofilter method only allows two criteria, and you are attempting 3. One way you could get around this it to add a new column, and populate it with a formula indicating whether you want to filter out or keep that row. Then just filter on the new column:

Please Login or Register  to view this content.

Another way that would work for your specific example would be to filter out all statuses (stati?) that are less than or equal to 80 and greater than or equal to 60. Because autofilter is set up to ask what you want to keep instead of what you want to get rid of, the code would be:

Please Login or Register  to view this content.

The problem with this code is it will also filter out if a status were in 75 or 74, even though you said you only wanted to filter out exactly 60, 70, and 80.

Oops, DaveGugg read poorly. I missed the part where you wanted to delete the rows as well. My stuff just filters. Rather than fix, since you've gotten two other answers, I'll defer to them.

CBG05QB,

I know there are responses already, but I came up with a solution as well, so I figured I'd post it just in case:

Please Login or Register  to view this content.

~tigeravatar

Look at the VBA help for the Range.Autofilter Method. You will see there are only two Criteria arguments available. It's just the way the Microsoft programmers decided to set up the method.

CBG05QB, I know it says solved but have a look at the link below as it explains the difference between xlOR and OR reasonably clearly

http://www.greytrout.com/manuals/SS_...e/node181.html