Copy visible cells only in 365?

Copper Contributor

The article says By default, Excel copies hidden or filtered cells in addition to visible cells. but when I copy filtered or hidden not included by default. Is there any setting for this in Excel 365?

 

"If some cells, rows, or columns on a worksheet do not appear, you have the option of copying all cells—or only the visible cells. By default, Excel copies hidden or filtered cells in addition to visible cells. If this is not what you want, follow the steps in this article to copy visible cells only. For example, you can choose to copy only the summary data from an outlined worksheet."

 

https://support.office.com/en-us/article/copy-visible-cells-only-6e3a1f01-2884-4332-b262-8b814412847...

9 Replies

we need to ask excel to ignore the hidden cells explicitly.

the following steps to copy the visible cells are given below:

1.select data as usual. But do not copy it yet.

2.Type F5 or Ctrl+G which opens the go to special dialog.

3.Click special button. This provides options to select specific cells depending upon the criterion you choose.

4.Choose visible cells only & click OK.

5.Copy the data now. Notice that only visible areas are copied.

6.Now when you paste, it will work as expected.

Hi,

My question was since Filtered is taking care of only copy pasting visible cells only then why to follow these steps? But this article saying different. Give a try on filtered and you will see only visible data copied. thanks

When I try to paste the cells after copying them in this way only part of the data will paste.  Any idea why I'm unable to copy and paste visible cells without excel omitting part of my data?

Hey MICROSOFT! Why the reverse-logic? Why wouldn't you have made the default behavior to operate on only visible cells? Then if for some bizarre reason I wanted to also work with hidden cells, then I could go to that Special place and SELECT HIDDEN CELLS.?? This counter-intuitive behavior always trips me up. I tell my students that for copies/cuts to just sort instead of filter to bypass this dangerous behavior.
Is it possible to put in an automathic way this process? I need to copy a lot of times the only visible cells and if evverytime I do have to use this procedure I will spend a lot of time (too much!)
if you are pasting to a filtered data, it will omit

better to use fill in this case
In that case you can use a Filter formula
Was this ever resolved? Is there still no way to copy+paste visible cells only in the web version of Excel?

@StefanoValenza 

With this macro you can easily copy-paste values from & to visible cells only.

 

It works like this:
Run the code > an inputbox will pop up > select the range to copy > OK > another inputbox will pop up > select the range to paste (select the first cell only) > OK.

 

If you need to use it frequently, you can put the code in a code module in the "Personal.xlsb" and then assign it to a button in the toolbar menu. So you just have to click the button when you need to run it.

Sub CopyVisibleToVisible1()
'use this for:
'Copy paste(value only):
'from filtered range to filtered range
'from filtered range to unfiltered range
'from unfiltered range to filtered range
'Not work on hidden column

'How it works:
'Run the code > an inputbox will pop up > select the range to copy > OK > anoher inputbox will pop up
' > select the range to paste (select the first cell only) > OK
    
'If you need to use it frequently, you can put the code in a code module in the "Personal.xlsb" and
'then assign it to a button in the toolbar menu. So you just have to click the button when you need to run it.
    
    Dim rngA As Range
    Dim rngB As Range
    Dim r As Range
    Dim Title As String
    Dim ra As Long
    Dim rc As Long
    
    On Error GoTo skip:
    
    Title = "Copy Visible To Visible"
    Set rngA = Application.Selection
    Set rngA = Application.InputBox("Select Range to Copy then click OK:", Title, rngA.Address, Type:=8)
    
    Set rngB = Application.InputBox("Select Range to Paste (select the first cell only):", Title, Type:=8)
    Set rngB = rngB.Cells(1, 1)
    Application.ScreenUpdating = False

    ra = rngA.Rows.Count
    rc = rngA.Columns.Count
    If ra = 1 Then rngB.Resize(, rc).Value = rngA.Value: Exit Sub
    
    
    Set rngA = rngA.Cells(1, 1).Resize(ra, 1)
    
    For Each r In rngA.SpecialCells(xlCellTypeVisible)
      rngB.Resize(1, rc).Value = r.Resize(1, rc).Value
        Do
          Set rngB = rngB.Offset(1, 0)
        Loop Until rngB.EntireRow.Hidden = False
    Next
    
    Application.GoTo rngB
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    

Exit Sub
skip:
    If err.Number <> 424 Then
        MsgBox "Error found: " & err.Description
    End If
    
    Application.ScreenUpdating = True
    Application.CutCopyMode = False

End Sub