0

I have a filtered spreadsheet that has 48 rows after filtering. I'm trying to copy and paste the top 24 rows into the bottom 24 rows but when I select the top 24 it groups them in 4's and will only paste the top 4. I have included a screenshot below.

excel only selecting 4 rows

Any ideas?

twigg
  • 425
  • 3
  • 9
  • 22
  • For clarity, you mean you are selecting the first cell and dragging the mouse down 24 cells (to select), and when you release the mouse button it automatically groups them? – Dave Nov 03 '16 at 11:16
  • @Dave yes that is correct, highlight the first, drag down to the 24th and let go, ctrl c and then ctrl v into the 25th cell – twigg Nov 03 '16 at 11:18
  • 3
    @Dave I believe it is something to do with the filtering, maybe selecting the hidden rows? I'm not sure – twigg Nov 03 '16 at 11:22
  • copy and paste work differently than normal when filtered data is selected, copied and pasted. I would do the same selection and then `Cntl-C` and paste the output on a notepad to see what comes out – Prasanna Nov 03 '16 at 11:48

2 Answers2

2

This is more about what is visible to you. I suspect everything is working fine, but, because you have filtered rows you're not seeing it, even in the paste! This is because Excel must be (I'm assuming) doing something to the entire row when you filter...

You have 2 options to work around this, and to further explain it.

Assuming you have filtered data from row 1 to 50. You'd need to make your selection, copy (as you have) but paste this after row 50 (with a normal paste). You will then see the paste is exactly as you want.

If you paste within the same rows as the filtered table you'll not be able to see the "correct" paste until after you've removed the filter (but the pasted values will be there as you want.

To paste on the same place (between row 1 and 50) right click and choose paste special -> paste picture... Yes, it will paste it as a picture so you can't use it to create formula but depending on your needs it may suffice

Another option is to paste to visible cells only (I don't think this is going to help you though)...

1) Copy the cells you want pasted.
2) Highlight range you want to paste in to
3) Go to Home ribbon, Editing box, Find & Select.
4) choose option - Go To Special
5) bottom right of the box choose Visible Cells only.
6) now paste.

Source

Finally, from the same source, the VBa option (not tested)

Sub Copy_Filtered_Cells()
    Set from = Selection
    Set too = Application.InputBox("Select range to copy selected cells to", Type:=8)
    For Each Cell In from
        Cell.Copy
        For Each thing In too
            If thing.EntireRow.RowHeight > 0 Then
                thing.PasteSpecial
                Set too = thing.Offset(1).Resize(too.Rows.Count)
                Exit For
            End If
        Next
    Next
End Sub

How do I add VBA in MS Office?

Community
  • 1
Dave
  • 25,297
  • 10
  • 57
  • 69
  • Thank you -- I'm just switching from Mac + Google Sheets to Windows + Excel and this was driving me MAD. – Ellen B Oct 16 '19 at 22:21
0

Excel is pasting all 24 cells into the unfiltered grid, so that only the first four values get to the intended cells. The others are copied to the rows currently hidden, possibly overwriting some important data.

It is a very annoying feature, but we have to live with it.

user36811
  • 119
  • 4