Excel pasting - How to keep cell-row relationship when pasting

Question

I have information in Excel on several rows. I want to copy and paste selected cells and keep each cell value on that row that it was copied from. But each time I copy the values they get clustered together on rows next to each other.

I have included an image to illustrate what I want and how it actually turns out. I select the cells in column B with the value "Banana" (B2 and B4). When I paste I want them to look like in column D (D2 and D4). But what actually happens is the result in column F (F2 and F3).

I use Excel 2016.

Answer 1

That behavior is by design; when you select noncontiguous cells and copy them, Excel doesn't remember their original location.

Workarounds:

• Copy the values one at a time to the target cells.
• Copy the entire column first, then filter it to exclude all cells with "Banana" and delete the contents from every other row.
• Use formulae to check whether "Banana" is in the same cell on the other sheet, and only return a value when it is. This formula should work: =IF(B2="Banana",B2,"")

• Use a macro to look at all cells in column B, and duplicate the value to column D if it says "Banana" Sub BananaCopy() Const csSrc As String = "B" Const csTgt As String = "D" Const csFruit As String = "Banana"

  Dim rngFruits As Range, rngCell As Range
  
  Set rngFruits = Range(csSrc & 1, csSrc & Rows.Count)
  For Each rngCell In rngFruits
      If rngCell.Value = csFruit Then
          Range(csTgt & rngCell.Row).Value = csFruit
      End If
  Next rngCell
  

  End Sub