• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 930
  • Last Modified:

How to Copy and Paste Visible Cells only

Hi Guys, in Excel if I want to copy a cell and paste the contents from the Cell down other cells in a Auto-Filtered Column. How do I do it without pasting over the Hidden rows?
0
Justincut
Asked:
Justincut
1 Solution
 
NorieCommented:
You could use SpecialCells(xlCellTypeVisible) to return only the visible cells in the range you want to copy to.

For example, copy D1 to visible cells in B2:B100.

Range("D1").Copy Range("B2:B100").SpecialCells(xlCellTypeVisible)

Open in new window

0
 
LCCSAMCommented:
Here is the microsoft write-up for what you are asking. The "Go To Special..." function is what you are looking for.

There is also a keyboard shortcut for this but it seems to be slipping my mind today.

http://office.microsoft.com/en-us/excel-help/copy-visible-cells-only-HA010244897.aspx
0
 
Rob HensonIT & Database AssistantCommented:
When copying a single cell and pasting into a filtered range, the default will be to only paste into visible cells anyway. Likewise using Ctrl+D to Fill Down will only populate visible rows.

The problem comes when you are copying a range of more than one cell. When pasting that it will paste into the same sized contiguous range.

Thanks
Rob H
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now