Using Filters with Copy/Paste

[Alex Blanchard]

Whenever a user selects Data>Filter>Autofilter and then
uses a filter on a column in the spreadsheet. If the user
tries to do a row copy, any hidden columns are not
included in the copy from range. So you cannot do a row
copy/paste if you have any hidden columns. If you paste
into a single cell, since the hidden columns are not
included in the copy range, the rows columns will not
allign properly when you do a paste. Seems like this is a
bug in Excel since it only occurs once a Filter is
actually selected.

 

[David McRitchie]

Hi Alex,
Things that are filtered out will not print and will copy for copy and paste,
that is by design it is not a bug. If is a very useful feature also if you are
using Mail Merge say for MS Word to print labels from your Excel spreadsheet
as the database.

Hidden rows and columns differ from filtered columns.

 

[David McRitchie]

Hi Alex,
Sorry I misworded that a little bit. What is left to see in a filtered
list is what can be copy and pasted, and is what can be seen by
Mail Merges. This is not a bug, it is a very useful feature.

Hidden columns are not the same as filtered out columns. Hidden
columns have zero width and can be copied.

 

[Alex Blanchard]

David,

Thanks for your reply. You state that things that are
filtered out WILL copy for copy and paste. That's really
not my problem, I'm only trying to copy things that are in
the filter. The problem is that hidden columns are not
being included in the copy range. Here's the example:
Let's say I have 20 rows of data with columns A to F also
filled with data. But column B is a hidden column. Now I
turn on auto filter and use column C to filter out the
first 10 rows. If I try to copy row 11 (select the whole
row) and paste it into a blank row, it will fail because
the copy range does not include column B. This only
happens once you apply a filter to the data, not when you
turn on filters. Why wouldn't I be allowed to copy a row
once I use a filter?

 

[Alex Blanchard]

You seem to state what I think should happen with
filtering. Filtering only filters out rows of data, not
columns. I don't believe there is any such thing as
filtering out columns. I would expect that copy/paste
would not be copying any rows that have been filtered
out. You state that hidden columns are just columns with
zero width and can be copied. --This is not the case.
After applying a filter, you cannot copy data in a hidden
column. They are omitted from the copy range (even though
I've selected the entire row). I think if you tried my
little example, you would be surprised at the results.

-----Original Message-----
Hi Alex,
Sorry I misworded that a little bit. What is left to see in a filtered
list is what can be copy and pasted, and is what can be seen by
Mail Merges. This is not a bug, it is a very useful feature.

Hidden columns are not the same as filtered out columns. Hidden
columns have zero width and can be copied.
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

user

.

 

[David McRitchie]

Hi Alex,

I made up test data that looks like this

aa B1 C1 D1 E1 F1 G1 H1
bb B2 C2 D2 E2 F2 G2 H2
bb B3 C3 D3 E3 F3 G3 H3
aa B4 C4 D4 E4 F4 G4 H4
aa B5 C5 D5 E5 F5 G5 H5
bb B6 C6 D6 E6 F6 G6 H6
bb B7 C7 D7 E7 F7 G7 H7
aa B8 C8 D8 E8 F8 G8 H8
aa B9 C9 D9 E9 F9 G9 H9

You can see a visual presentation in
http://www.mvps.org/dmcritchie/excel/filter_tst.htm

I have to work on my wording in the newsgroup. You filter on a
column, which filters out rows. Rows that are filtered out are
not seen and are not copied.

I know that filtered out rows do not get copied and that is a
terrific feature for Mail Merge and for Copy and Paste in that
you don't see them in the pasted data or in what is presented
to Mail Merge.j

But there were surprises to me along the lines of what you
pointed out.

-- when a filter has been invoked, hidden columns also do not
get copied.

-- When a filter has been invoked, hidden rows reappear if the
filter is showing the value filtered on.

-- When the filter is removed after filtering, there are no more
hidden rows.

-- Not a surprise but hidden columns remain hidden throughout
filtering or not filtering. When nothing is filtered out hidden
columns and hidden rows get copied as always.

-- When anything is filtered out, hidden columns do not get copied.
(same as 1st item, but repeating it anyway)

I don't know whether not copying hidden columns on rows that show
up when filtered is a bug or not. I don't know where the documentation
for this is. It doesn't bother me though -- just wish I could find it in
documentation.