Copy-Paste in Filtered Column (While Skipping Hidden Cells)What happens if you perform paste in a filtered column?

Written By:
Patrick Louie
Reviewed By:
FundsNet Staff

Which functions do you think are your most used ones in Excel?

It’s probably the copy and paste functions (a.k.a. copy-paste), right?

They might also be your most used functions in Word too. And I understand why.

They’re reliable, handy, and easy to use after all.

You can just copy a string, cell, or range of cells then paste it anywhere on the sheet or document.

However, when working with filtered data, you’re most likely to find out that copy-pasting isn’t as smooth as usual.

This is because when you filter data, Excel hides the cells (and their corresponding rows) that don’t meet your specified filter criterion.

And when you try pasting onto hidden cells, Excel will act up.

This doesn’t mean that you can’t copy-paste onto filtered data. While it may not be as smooth as usual, you can still do it.

You just need to be mindful of extra stuff.

In this article, we will be learning how to copy from a filtered dataset, as well as how to paste in a filtered column (while skipping hidden cells).

We’ll first try to copy the value from a single cell and paste it into the visible cells of a filtered column. Then we’ll try to do the same with a set of values.

Let’s get started!

How to Filter Data in Excel

Suppose we have the following data:

Say that you want to filter this data so that it only shows characters that are on the Loki server.

To do so, follow these steps:

  • Select the entire dataset.
  • Open the Data Tab. Then, click the Filter button which you can find in the Sort & Filter section.

  • You should now find buttons with the down arrow symbol on the right side of your column headers. These buttons will help you filter your data. Click the button to open filter options for a particular column.

  • In our example, we want to filter our data so that only plays on the Loki server show. To do so, click the button next to the Server header. This will open the filter options for that column. Uncheck “(Select All)” and then check “Loki”. Click the OK button.

  • The sheet should now only show players on the Loki server.

Copying From Filtered Data

Copying from a filtered column is pretty simple.

If you want to copy the visible rows, just select the visible rows.

Then press Ctrl+C on your keyboard to copy said rows.

Any hidden rows will not be copied.

This is because when copying from a filtered dataset, Excel will automatically only the visible rows.

To copy a visible cell or range of cells, you can also do the same above.

Just select the cell/s and press Ctrl+C on your keyboard.

You may come across an issue when copying visible from filtered data that has Subtotals or other similar features.

There’s an easy solution for this.

Select the visible rows, cells, or range of cells that you want to copy.

Then, press Alt+; (Alt key plus a semi-colon).

This will select only the visible rows, cells, or range of cells. Then press Ctrl+C on your keyboard to copy.

As you can see, copying from filtered data isn’t that hard.

Pasting in filtered data or a filtered column is a different beast though.

Pasting a Single Cell Value to All the Visible Rows of a Filtered Column

Let’s go back to our filtered data above:

Suppose that the Loki server will be absorbed by the Thor server.

This means that we have to change the Loki values in the Server column to Thor.

While we can do this via the Replace function, we can also use copy-paste to update the dataset.

  • Select a blank cell, preferably one that doesn’t belong to any row or column that has data. Type “Thor. Then copy this cell by pressing Ctrl+C on your keyboard.
  • Select all the visible cells in the column that has the “Server” header.
  • Paste the single cell value in the selected cells by pressing Ctrl+V on your keyboard. It should be pasted only on the visible cells. You can also press Alt+; on your keyboard before pasting just to make sure only the visible cells are selected.

  • To verify this, you can clear the filter on the Server column. Press the button beside the Server header and click on “Clear Filter From “Server”.

  • You’ll find that all the other values in the column remain unchanged.

Pasting a Set of Values to Visible Rows of a Filtered Column

Now when you try to do the same with a set of values, you’re most likely to encounter this error message:

This is because Excel does not allow pasting to cells in a range that contains hidden rows or columns (which, in our case, has hidden rows). This is just one of Excel’s limitations. Thankfully though, you can still work around this limitation.

Use a Formula to Paste a Set of Values to Visible Rows of a Filtered Column

The first workaround is to use a formula to copy the set of values. Let’s go back to our filtered data for now:

Suppose we want to copy the IGN of the players that will be transferred to the Thor server. We want to copy and paste their IGN into a new column (which will be column F). Here are the steps you have to follow:

  • Select a blank cell, preferably one that is in the same row as the value that you want to copy (in our example, this will be cell F4).
  • Press the = (equals) key and then click on the cell that you want to copy (which is cell A4 for our example).

  • Press the Enter The value in cell F4 should be the same as the one in cell A4 now.

  • Copy the formula in cell F4 by dragging down the fill handle until the last visible row. This will only paste the formula in the visible cells of column F.

  • You can verify this by clearing the filter. . Press the button beside the Server header and click on “Clear Filter From “Transferee”. You should find that the formula is only pasted on the concerned cells.

  • Now since we’re using a formula here, any changes in the reference cell will reflect on the cell that contains the formula. If you want the new column to be static, you can simply copy the column, then use paste special -> paste values on the same column.

Use a VBA Code to Paste a Set of Values to Visible Rows of a Filtered Column

Another method to paste a set of values to visible rows of a filtered column is to use a VBA code. Here’s the code that we will be using:

Sub paste_to_filtered_col()

Dim s As Range

Dim visible_source_cells As Range

Dim destination_cells As Range

Dim source_cell As Range

Dim dest_cell As Range

Set s = Application.Selection

s.SpecialCells(xlCellTypeVisible).Select

Set visible_source_cells = Application.Selection

Set destination_cells = Application.InputBox("Please select the destination cells:", Type:=8)

For Each source_cell In visible_source_cells

source_cell.Copy

For Each dest_cell In destination_cells

If dest_cell.EntireRow.RowHeight <> 0 Then

dest_cell.PasteSpecial

Set destination_cells = dest_cell.Offset(1).Resize(destination_cells.Rows.Count)

Exit For

End If

Next dest_cell

Next source_cell

End Sub

Be sure to copy all of it.

  • Open the Developer tab. Then, click on the Visual Basic button. This will open the VBA window.

  • Click on Insert then select Module.

  • In the Module window, paste the code that we have above.

Now that we created our macro, all we need to do is run it.

  • Select the cells that you want to copy.
  • Run the macro script. To do so, open the Developer tab. Click on the Macros button. Select the macro that we just created (which is probably named “paste_to_filtered_col”). Then press the Run button.

  • The macro will prompt you to select the destination cells. Select the cells where you want to paste the copied values.

  • Click the OK button. The copied values should be pasted on the selected cells. You will also find that it is only pasted in the visible cells.

Conclusion

While it may not be that easy to copy-paste onto filtered data while skipping hidden rows, it’s still doable.

And this article is proof of that.

Here, you’ll find three ways to paste only onto Visible Rows of a Filtered Column.