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
  • 455

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,513
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