One of the easiest actions to perform in Excel is to copy and paste. Every Excel user relies on it when performing their daily task. However, pasting in Excel when filter is applied can be complicated. If you are facing this problem, then this tutorial is for you.
How to copy in Excel when filter is applied
As you can see in the image below, some of the delivery dates of all Asus products are missing. However, from the sheet provided, the order date is the same as the shipping date.
Our first step is to filter this data to remain with only products with missing shipping dates.
To apply the filter, just highlight all data by using your mouse or the shortcut ctrl+A. Thereafter click on the data tab>filter Icon.
As you can see from the image above, once you press the filter button, a small drop-down arrow icon is added to all the columns. Now you can press the icon on the shipping date column to filter the data.
To remain with the blank spaces of the Asus product, all I need to do is tick the blanks option and, untick all other options, then press Okay.
By pressing okay, all data will be filtered, so I will remain with Asus products only that have empty shipping dates. You can tell the rows have been filtered by checking the number column on the left side. If its numbers are blue rather than the usual black colors, then the filter is applied. Additionally, you can tell easily because some row numbers are missing. For example, in the data below, only rows 1, 4, 7, 9, 11, and 14 are visible.
To copy from visible rows when the filter is applied is easy. All I need to do is highlight the visible cells and click on copy located on the toolbar under the home tab.
Alternatively, you can use the shortcut ctrl+c to copy when a filter is applied
How to paste in Excel when filter is applied
Now after copying, you need to paste the data to the visible cells. However, as you can see from the image below, that doesn’t work. This is because many rows have been filtered out. It is one of Excel’s software limitations.
For this reason, I have prepared various ways you can follow to paste in Excel when filter is applied. These methods will help you paste into the visible rows.
Use Formula
1. Type the “=” in the first cell of the visible cells where you wish to paste. For example, in this case, it is “E4.”
2. Type in the first cell where you wish to copy the data. In this case, it’s “A4.”
3. Press enter. This will make cell “A4” a reference cell for “E4.”
4. Drag down the following formula to the rest of the visible cells using the fill handle.
As you can see from the image above, all the missing data has been posted to the visible cells.
5. If you wish to verify that the data pasted is correct, compare column A and Column B. Alternatively, remove the filters o see whether all the shipping dates are visible.
6. To remove the filter, all you need to do is highlight all the data, press on the Data tab, and then click the filter icon.
As you can see from the image above, all the data is clean and intact.
Use Kutools Addin
Kutools is an Excel addin by Extend Office. It provides more functions that simplify Excel functions and also help to mitigate its limitations. It provides an easy-to-use interface and adds extra tools to the Access menu.
1. Download and Install Kutools from the official website
2. Copy the data you wish to paste on visible cells
3. Click on the Kutools Tab, then on the range drop-down, then choose the “paste to visible” option, and finally, “All.”
A pop-up menu will show, prompting you to enter the range that you wish to paste the copied data.
4. Highlight the visible cells to enter the range automatically
5. Press Okay, and Excel will paste the copied data into the visible cells only.
Download how to paste in Excel when filter is applied Practice Sheet
Using VBA Code
VBA stands for Visual Basic Applications. Excel allows users to create micro programs popularly known as macros. These programs add more functionality or make work easier. You can use VBA to paste in Excel when filter is applied.
1. Click on the developer tab, then Click on Visual Basic to launch the VBA editor. You can also launch the VBA editor by using the shortcut ALT+F11 on Windows and Opt+F11 on Macbook.
2. Once the VBA editor launches, click on insert, then select module
3. Copy and paste the VBA code below to the Editor
Sub paste_to_filtered_col()
Dim visible_source_cells As Range
Dim destination_cells As Range
Dim source_cell As Range
Dim dest_cell As Range
'Get visible source cells
Set visible_source_cells = Selection.SpecialCells(xlCellTypeVisible)
'Get destination cells
Set destination_cells = Application.InputBox("Please select the destination cells:", Type:=8)
'Loop through visible source cells
For Each source_cell In visible_source_cells
'Loop through destination cells
For Each dest_cell In destination_cells
'Check if row height is not zero
If dest_cell.EntireRow.RowHeight <> 0 Then
'Paste source cell to destination cell
dest_cell.Value = source_cell.Value
'Move destination cell range down
Set destination_cells = dest_cell.Offset(1).Resize(destination_cells.Rows.Count)
'Exit loop through destination cells
Exit For
End If
Next dest_cell
Next source_cell
End Sub
4. Head over to the back of your document and copy the data you wish to paste on visible cells
5. Click on the developer tab, select Macros, then run the Macro named “paste_to_filtered_col”
6. A pop-up menu will ask you to enter a range. Highlight your range using the mouse or type it and press okay.
7. The VBA code will paste the contents to the visible cells successfully.
Final Thoughts
In this article, we have discussed three simple methods that you can use to paste in Excel when filter is applied. No matter which method you choose, you will be able to perform this action quickly and accurately.
Similar Articles