How to copy a table with a filter into Excel. Paste into visible rows in Excel. Copying only visible cells

09.09.2021 Mobile OS

Conditional Formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (4)

How to paste copied cells into visible/filtered cells only

In general, the meaning of the article, I think, is already clear from the title. I'll just expand it a little.

It's no secret that Excel allows you to select only visible rows (for example, if some of them are hidden or a filter is applied).

So, if you copy only visible cells in this way, they will be copied as expected. But when you try to paste something copied into a filtered range (or containing hidden rows), the result of the paste will not be exactly what you expected. Data will be inserted even into hidden rows.

Copy a single range of cells and paste only into visible ones
To insert data only into visible cells, you can use the following macro:

Option Explicit Dim rCopyRange As Range "With this macro we copy the data Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else : Set rCopyRange = ActiveCell End If End Sub "With this macro we insert data starting from the selected cell Sub My_Paste() If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "The pasted range must not contain more than one region!",vbCritical, "Invalid range": Exit Sub Dim rCell As Range, li As Long , le As Long , lCount As Long , iCol As Integer , iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange .Columns.Count li = 0: lCount = 0: le = iCol - 1 For Each rCell In rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset (li, le).EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row - rCopyRange.Cells(1 ).Row Next rCell Next iCol Application.ScreenUpdating = True : Application.Calculation = iCalculation End Sub

Option Explicit Dim rCopyRange As Range "Use this macro to copy the data Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub "Use this macro to paste the data starting from the selected cells Sub My_Paste() If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "The pasted range must not contain more than one area!", vbCritical, "Invalid range": Exit Sub Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange.Columns.Count li = 0: lCount = 0: le = iCol - 1 For Each rCell In rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset(li, le).EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row - rCopyRange.Cells(1).Row Next rCell Next iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub

To complete the picture, it is better to assign these macros to hot keys (in the codes below, this is done automatically when opening a book with the code). To do this, you just need to copy the codes below into the module ThisBook (This Workbook) :

Option Explicit "Cancel the assignment of hotkeys before closing the workbook Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub "Assign hotkeys when opening the workbook Private Sub Workbook_Open() Application.OnKey "^q", "My_Copy": Application.OnKey "^w", "My_Paste" End Sub

Now you can copy the desired range by pressing keys Ctrl + q , and insert it into the filtered one - Ctrl + w .

Download example

(46.5 KiB, 9,622 downloads)

Copy only visible cells and paste only into visible ones
At the request of site visitors, I decided to refine this procedure. It is now possible to copy any ranges: with hidden rows, hidden columns, and paste copied cells also into any ranges: with hidden rows, hidden columns. It works exactly the same as the previous one: by pressing keys Ctrl + q copy the desired range (with hidden/filtered rows and columns or not hidden), and paste with a keyboard shortcut Ctrl + w . Insertion is also performed in hidden/filtered rows and columns or without hidden ones.
If the copied range contains formulas, then to avoid reference displacement, you can copy only the cell values - i.e. When inserting values, not formulas will be inserted, but the result of their calculation. Or if it is necessary to preserve the formats of the cells into which the insertion occurs, only the cell values will be copied and pasted. To do this, you need to replace the line in the code (in the file below):

rCell.Copy rResCell.Offset(lr, lc)

to this:

rResCell.Offset(lr, lc) = rCell.Value

Both of these lines are present in the file below; you just need to leave the one that is more suitable for your tasks.

Download example:

(54.5 KiB, 7,928 downloads)

Also see:
[]

Did the article help? Share the link with your friends! Video tutorials

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; text-align:left;","textbgcss":"display:absolute; left:0px; ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

If some cells, rows, or columns in a worksheet aren't visible, you can copy all the cells (or just the visible cells). By default, Excel copies not only visible cells, but also hidden or filtered cells. If you only want to copy visible cells, follow the steps below. For example, you can copy only summary data from a structured worksheet.

Follow the steps below.

Note: When you copy, values are inserted sequentially into rows and columns. If the paste area contains hidden rows or columns, you may need to unhide them to see all the data you copied.

When you copy and paste visible cells in a data range that contains hidden cells or that has a filter applied, you may notice that the hidden cells are pasted along with the visible cells. Unfortunately, you can't change this setting when you copy and paste a range of cells in Excel for the web because Pasting only visible cells is not available.

However, if you format your data as a table and apply a filter, you can copy and paste only the visible cells.

If you don't want to format your data as a table and you have desktop Excel installed, you can open the workbook in Excel to copy and paste visible cells. To do this, click the button Open in Excel and follow the steps in Copying and pasting only visible cells.

More information

You can always ask a question to an Excel Tech Community specialist, ask for help in the Answers community, and also suggest new feature or improvement on the website

Data selection in Excel was carried out using a filter or sorting. Now they need to be printed or moved to another location. Copy to Excel configured so that hidden cells are also copied.
Let's consider two ways, To How to copy filtered rows in Excel.
First way.
There is a great function in Excel - Paste Special function in Excel.
So we have a table.
How to install a filter, see the article " Filter in Excel ".
We use a filter to remove all Ivanovs from the list. It turned out like this.
Select the table and click “Copy” in the context menu. Left-click cell A9 and select “values” in the context menu.
Click "OK". Voila. Not only the value of the visible rows was copied, but also the format of the cells.
There is one nuance- insert filtered data not into the rows where the filter is located. For example, in our example - not in lines 1-7, but below or on another sheet, etc. If we paste into the rows where the filter is located, then the filtered data will also be inserted into the rows hidden by the filter. In general, it will turn out to be a mess. Second way.
The table is the same. Select the table with filtered data. On the “Home” tab, click in the “Editing” section Find and Highlight functions in Excel. Then, click the “Go” button. In the dialog box that appears, click the “Select...” button. In the “Select a group of cells” window, check the box next to “only visible cells”. Click "OK". Now on the same selected table with the right mouse we call context menu. Click the “Copy” function. In a new location (in our example, this is cell A15), click “Insert”. All. It turned out like this.
How, without copying, immediately print filter data in excel, see the article "Bookmark Excel sheet"Page Layout"

Paste only into visible lines inExcel numbers, formulas, text can be done in several ways. When you need to insert numbers, formulas, text into not all rows of the table, you can use a filter. How to install a filter and how to filter in Excel, see the article “Filter in Excel”. But to insert data only into visible cells, you need your own methods, especially if there are many rows.
The first way is ordinary .
Let's take a table like this. The table will be the same for all examples.
Let's use a filter to remove all digits 2 from the table. In the remaining visible cells we put the number 600. In cell B2 we put the number 600, then copy it down the column (pull the lower right corner of cell B2). The values were copied only to visible cells. You can also insert formulas in the same way. We write the following formula in cell C2. =A2*10
It turned out like this.
Let's cancel the filter. The result is a table like this.
The formula and numbers were inserted only into the filtered rows.
Second way.
We will also filter the data. In the first cell we write a number, formula, text, etc. Now, if there are thousands of rows, then select the cells like this: press the keys “Ctrl” + “Shift” + the down arrow button (or the up button, depending on where we want to select the cells - below or above the cell in which the number was written) .
Now, or press the key combination “Ctrl” + G, or the F5 key. The Transition dialog box will appear. Click the “Select...” button. And, in the new “Select a group of cells” dialog box, check the box next to the words “Only visible cells”.Click "OK". Then insert as usual.

Another way to bring up the Select Group of Cells dialog box.On the “Home” tab, in the “Editing” section, click on the “Find and Select” button. In the list that appears, click on the “Select a group of cells” function.

To fill visible cells in selected Excel columns, press the key combination “Ctrl” + D. And all selected columns will be filled with data or a formula, as in the first cell. In our example, we wrote the number 800 in cell D2, column D.

Third way.
In a new column (in our example, column E), select the cells. Press the F5 key. The Transition dialog box will appear. Click the “Select...” button. And, in the new “Select a group of cells” dialog box, check the box next to the words “Visible cells only”. Click "OK". Now, without canceling the selection, in the first cell of the column (ours is E2), enter a formula, number, etc. Press the key combination “Ctrl” + “Enter”.