How can I get Excel 2007 to consider only filtered cells?

[Bjarne Fosse]

I have switched from Excel 2003 to Excel 2007, and unfortunately the filter
function seems to be different. If I apply a filter to my data range in Excel
2003 I can perform operations to a range of cells without touching the hidden
rows in the same range, i.e. deleting, auto-fill and copying is only relevant
for the visible rows.

If I try to do the same in Excel 2007 the hidden cells in the range are
suddenly included in all operations. This means that if I have applied a
filter which only shows row 2, 4 and 6, copying row 2-6 will include row 3
and 5 when pasting the range into another sheet.

Is this a bug in Office 2007?
If yes: Where can I find a patch for this?
If no: Is it possible to change this behavior by editing any setting in
Excel 2007?

 

[Roger Govier]

Hi

Instead of using Autofilter, use Insert tab>Table>my table has headers.
This will create filters for you, and there is not the problem you describe.
The Table knows the bounds of the data.

With Autofilter, if your currently selected cell is in the column where you
apply the filter, before selecting the filtered item, then it does behave
properly. If your currently selected cell is in a column which is not
included in the Autofilter, then you click an Autofilter button to make a
selection, then the behaviour is as you describe.

There is a patch to fix the but, but I can't seem to find it at present.
The above workarounds should keep you going until you get the patch.

 

[Dave Peterson]

I saw this response in a different forum:

The key here is that if you select just the table (or cells in the same rows
as the table) to copy, then you will get the filtered data.

If you select the filtered cells, plus data outside the table (the row
above/below/both) then you will get the entire list (unfiltered).

You can tell what you are going to get by looking at the "running ants"
selection after pressing Ctrl+C. If you see only one area with the running
ants around the outer perimeter, you are going to get the whole table. If
you see several 'mini-selections' inside a larger bold border, then you will
get the filtered data.

============
Personally, I just use an extra step.
I select the filtered range.
F5 (or ctrl-g)|Special|visible cells only
(alt-; (alt-semicolon) will select the visible cells in the current selection,
too.)

Then do the copy|paste.