Delete Filtered Data

**erock24** · 03-16-2010, 03:32 PM

I'm trying to have a macro filter a range then delete the results if there are any. I have successfully created the code to filter the range, but I am unable to correctly delete the results. I'm trying to delete only the visible cells in that range, but the macro for some reason seems to be deleting the entire row even though i'm not telling it too. What's wrong??

Note: I posted this here a few days ago, with no luck: http://www.mrexcel.com/forum/showthread.php?t=454229

Please Login or Register  to view this content.

**Leith Ross** · 03-16-2010, 03:51 PM

Hello erock24,

In your code you are extending the range to the bottom of the worksheet by using Rows.Count. Because the SpecialCells property has a limit of 8192 entires, you will need to limit the range to the last entry in the search range. Once this limit is exceeded, things get weird.

**erock24** · 03-17-2010, 04:29 PM

Leith,

Thank you for the reply. I edited the code to only go to the bottom of the filtered data. I get the same result. The code seems to be deleting the entire row, when I only want cells within my designated range to be deleted. Is this possible or does the code always default to entire rows in filter mode??

Please Login or Register  to view this content.

**Leith Ross** · 03-17-2010, 05:23 PM

Hello erock24,

Can you post your workbook for review?

**erock24** · 03-19-2010, 11:18 AM

Sorry for the late response....attached is a sample with the filter code. The filtering works great. The code will filter the blue range 1st, then yellow, then green. It will not find anything in blue or yellow, but it will in green. But then the code doesn't work right. Instead of deleting just the green filtered range, it seems to delete entire rows.

**Leith Ross** · 03-19-2010, 09:46 PM

Hello erock24,

As I understand your code, it is supposed to delete only the rows within each group if there is a zero in the fifth column. If that is correct then this macro will do that.

Please Login or Register  to view this content.

**erock24** · 03-22-2010, 09:39 AM

Thank you for your reply. I already have similar code that loops through every cell stepping backwards through the ranges checking for 0 and deleting. The code works fine as does yours, but I was looking for something faster. In the real file the ranges are many thousands of rows and the code takes forever. That's why i thought the autofilter would be better. However, if looping is the only way then so be it.

Thank you for your time and help.

**Leith Ross** · 03-22-2010, 11:30 AM

Hello erock24,

The drawback with filtering is it effects the entire row. You need to effect change only on specific columns and looping is the only way to accomplish that.