Autofilter is Harmful to your Data

[Sean]

When Autofilter is applied to columns of data, it should be that only those
rows still showing are affected by actions such as delete, format,
search/replace. It always works that way with XL2003, but with XL2007, when
you first use the Autofilter, even hidden rows are affected, which can result
in the loss of vital data - Not amusing. Generally, if you clear the filter &
then apply another filter (or simply reapply the first) , it works properly,
but until you have done that, you are risking your data. This is a MAJOR
problem - How come there's no chat about it? Maybe MS would even consider
adding it to the long list of other features that need fixing in XL2007.
Sean

 

[JMB]

It always works that way with XL2003

If you delete the data using Edit/Delete, then your hidden data is fine.
But if you use the delete key then the hidden data is also deleted. I've not
used XL07 yet, but what you describe is how it works for me on XL00 and XL03.

If I want to perform actions on the filter results, I normally select the
column, then Edit/Goto/Special/Visible Cells only, and do what I need
(format/search replace/delete using delete key). Not sure how that's done in
XL07, though.

 

[Sean]

Actually, I always found that even just hitting <delete> also spared the
hidden cells, without having to go to them explicitly, so I never used
GoTo\Visible Cells. I expected the same behaviour in 2007 but I don't get it,
at least until the filter has been modified or reapplied. Interestingly, in
2007, after first applying filters, <Delete> deletes hidden cells, while
GoTo\Visible Cells & Delete does not.

Sean

 

[JMB]

my apologies - you are correct. hitting the delete key does not delete the
hidden cells in previous versions. not sure what it was I was thinking
of....