To Work with the Visible Cells Only
  Back to List of Topics

There are many occasions where you desire to work with only the visible cells.  When you highlight cells that surround hidden rows and columns, the next command may or may not affect the hidden cells.

To work with only the visible cells:

  1. Select the range of cells to be copied
  2. Click on  Edit (in the menu)  GoTo  or press  F5
  3. Click on the Special Button
  4. Click on Visible Cells Only and click on OK

  5. At  this point only the visible cells are selected.  From here you proceed as normal with the desired command

This feature is straight forward, the tricky part is knowing of its existence.  It's not in a obvious place in the menu or button bar.  Now that you are aware of it, you can use for more that copying, you can do most anything you can do with highlighted cells.

Excel does not always act consistently with hidden rows and columns.  You may have hidden rows, especially with the subtotal and auto filter features.  You may highlight the desired cells and copy and paste all the time with no adverse effects.  Then, one day, the hidden cells, get copied as well.  Rather than beating your head against the wall, just repeat your copy steps, but use the  GoTo  Visible Cells Only  feature, and continue as normal.

VBA/Macros-You may desire to write a macro to perform a repetitive copy command than involves hidden cells. If so, you must modify the macro to select the  Visible Cells Only,  as the macro will copy the hidden rows, even though the manual process does not.

Copyright 1996, 1998, 2001 by Info-Stat, All rights reserved.
Rev 1/96, 3/98, 6/01