Excel – Copy/paste filtered column in Excel – error message

microsoft excel

In short, I have filtered a worksheet by column A, and I want to copy/paste from column B to column C. Obviously I don't wish to copy/paste values from rows that were filtered-out here.

The above sounds ridiculously simple, right?

  1. First I tried simply copy/pasting on the filtered worksheet. This appeared to select and copy only the filtered data, however pasting appeared to insert values into hidden/filtered rows – as you might expect.

  2. So my initial research suggests I may wish to select the filtered data and press Alt+;, which is a shortcut key for Goto Special > Select Visible. Then just copy-paste.

    CTRL+C correctly copies the filtered data, however when I go to paste the values into another column, it pastes into hidden rows as well.

  3. Okay, so perhaps I should also "Select Visible" on the cells I wish to paste into as well? Nope – that gives me the error That command cannot be used on multiple selections.

What am I doing wrong?!

Best Answer

Yes, I agree this is stupid. You can do it using the 'fill handle' - select the cells you want to copy and drag the fill handle across to copy them into the next column. This only works for a contiguous selection, and only if you're copying into the adjacent column. But presumably you could always move your column temporarily. I can't see any other way to do it without recourse to VBA.

Related Question