Filter Test, AutoFilter and Hidden Rows/Columns

Location: http://www.mvps.org/dmcritchie/excel/filter_tst.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Auto Filter

The original test data

	A	B	C	D	E	F	G	H
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)

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)

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"

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

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

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.

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.

(placeholder)
(placeholder)

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc 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

	A	B	C	D	E	F	G	H
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

	A	B	C	D	E	F	G	H
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