Filter Test, AutoFilter and Hidden Rows/Columns

Home page:
[View without Frames]

Auto Filter

The original test data

 1 aa  B1  C1  D1  E1  F1  G1  H1
 2 bb  B2  C2  D2  E2  F2  G2  H2
 3  bb  B3  C3  D3  E3  F3  G3  H3
 4  aa  B4  C4  D4  E4  F4  G4  H4
 5  aa  B5  C5  D5  E5  F5  G5  H5
 6  bb  B6  C6  D6  E6  F6  G6  H6
 7 bb  B7  C7  D7  E7  F7  G7  H7
 8 aa  B8  C8  D8  E8  F8  G8  H8
 9 aa  B9  C9  D9  E9  F9  G9  H9
Original test data:  You may copy from this table into Excel and delete the first column copied to show a table in Excel as above.

Hide Red Columns (C:E)

filter_b01   filter_b01
After hiding the red columns (c:e) the columns are hidden, but the hidden columns will still copy with copy (ctrl+c) and paste (ctrl+v).

Hide Red Rows (3:6)

filter_b02   filter_c01
With rows 3:6 hidden and the columns c:e also hidden, will still copy with copy (ctrl+c) and paste (ctrl+v) all rows and columns whether hidden or not.

Fiter to show "bb"

filter_b03   filter_c03
By then filtering to show "bb" hidden rows with "bb" become visible and all other rows disappear witht the filter.  Copying (ctrl+c) and paste (ctrl+v) copies only what is visible.  The unexpected result is that hidden columns do not show where before hidden columns would copy.  Rows that have been filtered out (made to disappear) are not copied which was expected.

Still filter to show "BB" but unhide Columns

filter_b04   filter_c04
What is seen with the filter invoked is what is copied.  This is like the normal nothing hidden but filtering invoked.

Not shown but if Columns were hidden they will remain hidden, when filter it turned off.

Show ALL in Column A, shows that the hidden rows are no longer hidden

filter_b05   filter_c01
Show ALL in Column A, shows that the hidden rows are no longer hidden, and so both the table and the copy look like the original test data.

Summary of Filtering related to hidden columns and/or hidden rows

Hidden rows and/or hidden columns make no difference to copy and paste, all rows and columns get pasted whether hidden or not.

You filter on a column, which filters out rows. Rows that are filtered out are not seen and are not copied.

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.

But there were surprises to me along the lines of hidden rows and columns.

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news:// where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
This page was introduced on March 16, 2004. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved