Microsoft Excel - paste without showing hidden rows (keep hidden)?

Question

How do I paste in Microsoft excel and keep hidden rows hidden? I know there's an option only to paste all visiable rows/columns - but this doesn't keep the information in the hidden rows available when you paste. I know there's an option to paste format...but this only helps with hidden columns. Please help??

Answer 1

The only options I can think of end up eliminating the hidden data. It is weird that Excel only give you an option to copy column widths and not row heights, at least in my version (2000.)

If you have time to prepare in advance of the copy/paste, you can set up a blank sheet with all the proper hidden rows and when you paste in, the rows stay hidden. This doesn't help you if your hidden rows change from event to event. It is just changing when you go and hide your rows from after your paste to before your paste.

Can you arrange the data in columns instead of rows, do the paste operation and later select only visible, then copy and paste transpose. This still removes the hidden data, but you can keep the hidden properties in your column arranged section.

Good Luck!

A note: I MS Access this can easily be accomplished with Filters, if your hidden data can be segregated by values in the data. Are you trying to put in a screw with a hammer?

Answer 2

Option 1: If you want to copy the entire rows (not only selected cells):
I will consider that you want to copy rows from sheet to sheet for simplification:
1- Select all the rows that you want to copy including the hidden rows.
2- (Ctrl + C)
3- Go to the other sheet, and select a row.
4- Right click on the selected row, and then choose "Insert Copied Cells"
5- Go again to the original data, then copy the columns and apply "Paste Special" to the destination columns by choosing "Column widths" only.
Note: Remember that when you hide the destination columns, their existing data will be hidden also.



Option 2: If you want to copy only selected cells including hidden cells in columns and rows:
1- Select the cells that you want to copy including the hidden cells.
2- (Ctrl + C)
3- Go to the destination and paste them (Ctrl + V)
4- Go to the original data, select the rows that containing the original selection, then apply match format from the original rows to the new rows (using "Format Painter" button in the Standard toolbar)
5- Go again to the original data, then copy the columns and apply "Paste Special" to the destination columns by choosing "Column widths" only.
Note: In this case the destination rows may contain existing cells with data, and when you copy format from the original rows, the existing cells will be affected by the new format.
Also, when you hide the destination columns, their existing data will be hidden.

Answer 3

Edit Menu -> Go To -> Special -> Radial button - Visible Cells Only