Excel: Copy Visible Cells only

I was looking for a way in Excel to copy the selection but only of the visible (not hidden) cells.

I share here my solution.

Built-in way

The official way in Excel requires quite a few clicks.

Click Home>Find & Select (in Editing Group on the right) and Go To Special

Click Visible cells only>OK

Then normally copy.

The setting sticks for the next copy actions.

VBA

In VBA it is a one-liner:

Selection.SpecialCells(xlCellTypeVisible).Copy

I have added a custom button for this in my excel adding because the normal way is easy to access and remember.

(If you are interested in how to add this to an Excel Add-In comment on this post.)

I use this quite a lot especially for copying filtered data from an excel table.

Filter the table, click in the table and Ctrl+A, copy visible.

See also

Copy visible cells only (Microsoft Support)