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?

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.
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.
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 Solutions

Excel – Copy/Pasting data from SQL Server to Excel splits up text into multiple columns

If you've used Text to columns recently, it will apply those settings to the Paste operation. I don't remember this happening before Excel 2007, but it may have. I always have to go back and set it back to tab delimited before I start copying and pasting again.

Mac – Chart macro displaying incorrect labels from non-sequential visible rows on filtered worksheet

Your diagnosis is correct -- VBA ignores hidden/unhidden status when parsing cell references with .Cells. A brute-force cell-counting method is the only thing I've found to work:

Dim iter As Long, findCount As Long, workCel as Range

' This is okay as long as you are guaranteed only to have one header row.
Set workCel = Worksheets("MySheetName").Cells(2, 1)

' No cells found yet
findCount = 0

' Start iterator at zero
iter = 0

Do  
    ' Check row for hidden status
    If Not workCel.Offset(iter, 0).EntireRow.Hidden Then
        ' Row is visible; increment number of visible rows found
        findCount = findCount + 1
    End If

    ' Increment iterator
    iter = iter + 1

' Stop looping once the number of found rows reaches the desired count
Loop Until findCount >= Arg2

The needed index should fall out of the above code as the value of iter:

sid = .Cells(iter + StartRow, "D") & vbLf & ...

No post-decrement is necessary, because .Offset(n, 0) refers to the n+1^th row of a range starting on a given cell.

To note, the .SpecialCells(xlCellTypeVisible) function is probably working correctly. The problem is, since the Range is 'interrupted' by the various hidden rows, it consists of multiple Areas (see here: http://msdn.microsoft.com/en-us/library/office/ff196243(v=office.15).aspx). This completely blows up normal .Cells(...)-type indexing. It's starting from the first row of data because your header row is unhidden & thus anchors your r range.

Best Answer

Related Solutions

Excel – Copy/Pasting data from SQL Server to Excel splits up text into multiple columns

Mac – Chart macro displaying incorrect labels from non-sequential visible rows on filtered worksheet

Related Question