3

I have two worksheets s1 and s2.

Data on s1 looks like:

+---+-----------+--------+------+
|   |     A     |   B    |  C   |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
+---+-----------+--------+------+

On s2, column B is hidden, like this:

+---+---+---+---+
|   | A | C | D |
+---+---+---+---+
| 1 |   |   |   |
+---+---+---+---+

When I try to copy 's1'!A1:C1 to 's2'!A1:D1, I get this:

+---+-----------+------+---+
|   |     A     |  C   | D |
+---+-----------+------+---+
| 1 | scarecrow | lion |   |
+---+-----------+------+---+

But I want the paste to ignore the hidden column B and give me this:

+---+-----------+--------+------+
|   |     A     |   C    |  D   |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
+---+-----------+--------+------+

How can I make the paste only apply to visible cells?

8
  • 2
    No, there isn't an easy way to do this without VBA – Raystafarian Apr 7 '14 at 18:46
  • This would seem like a common enough operation, once you start making use of hidden columns and rows. – Dane Apr 7 '14 at 19:44
  • You inspired me to write an AutoHotKey script to handle this situation. Are you interested in an AutoHotKey solution? Or is that too far out of the scope of this question? – Dane Apr 7 '14 at 21:17
  • @Dane, if you translate it to VBA, I'm interested. In this case I don't want a solution that relies on a 3rd party scripting language. – sigil Apr 7 '14 at 21:23
  • 1
    In case somebody wants the AHK solution: gist.github.com/Fluffums/10162893#file-ahk-literaltabpaste-ahk – Dane Apr 8 '14 at 18:22
1

To Paste data and skip hidden rows at destination do the followings:

  1. Select the Destination Range.
  2. Press F5 then select Special then Visible Cells Only.
  3. Press = and click on the first cell at your Source Range.
  4. Press Ctrl + Enter.

Note, this should bring in data from the visible rows provided their position is identical to the destination range.

2
  • this is not working for me on excel 2013, its the "press =" part that is throwing me off (it just inserts the = sign into all of the selected row, and after that im not allowed to paste). – James Gaul Mar 20 '20 at 23:43
  • @JamesGaul,, nice to hear from U, I've tested in 2013 and in 2016 also, and it's working,, do it like this ,,, Copy then select destination (including hidden cells/rows) press F5 select Visible cells only,, then enter = sign then hit Source data,,, and press Ctrl+Enter,, you get it done !! – Rajesh S Mar 21 '20 at 5:47
0

You can select "visible only" and then paste.

  1. Select range of cells.
  2. Find & select dialog -> Go to special
  3. Select "visible only" - now you selected only visible cells.
  4. All you have to do now is copy & paste.

More details here: http://office.microsoft.com/en-001/excel-help/copy-visible-cells-only-HA010244897.aspx

1
  • 2
    Sorry, I don't think I was clear about what I want. There are no hidden cells in the "from" range, only the "to" range. I want to select all cells in the "from" range, and then paste them so their values get set in the visible cells in the "to" range. – sigil Apr 8 '14 at 15:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.