VBA To Copy Visible Cells Only and Paste in Excel

Excel VBA To Select & Copy Visible Cells

In Excel, sometimes few rows wont be in visible state, when auto-filter is enabled or made hidden by any user.

In such cases, if we use ‘Selection.copy’ or ‘Range.copy’, it will copy both visible & hidden rows.

To avoid this, we have the below listed options to select & copy visible cells only in a filtered range..

1. VBA: Copy visible cells only
2. Manual: Excel copy visible cells only – Click here to get this option.
   • (a) Keyboard Shortcut to Copy only visible cells.
   • (b) Menu option

The first one is for Excel programmers to automate this process. The other 2 options are Manual methods.

Excel Copy Visible Cells Only VBA

How to hide rows: Add a auto-filter over a column header & select any value in the filter. This will hide rows that does not meet the selection criteria.

How to copy only the visible rows: You can use .Specialcells(xlCellTypeVisible) for this purpose as explained in below code snippets.

1. First example will just select all visible cells in the active sheet.
2. Second routine will copy non-hidden cells in a specific range.

Example1:

Option Explicit
Sub Copy_Visible_Cells_Only()
    'Select only the visible cells in active sheet
    'Code from Officetricks.com website.
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select

    'Since only the visible cells are selected. Only they are copied and pasted to target cell
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
End Sub

Example2:

The second routine, get a specific range in sheet as parameter. Only the visible cells in this range will be copied to the destination sheet.

Sub Select_Visible_Cells_In_Range(Rng As Range)
    'Select visible cells in the chosen Range
    Rng.Select
    Selection.SpecialCells(xlCellTypeVisible).Select

    'Copy Visible cells only in the range and paste in target sheet
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
End Sub

If you just select the table or all rows and paste it in another sheet, then Excel will paste all the visible and hidden rows also.

The option ‘xlCellTypeVisible’ can be used within VBA range object as explained in above codes sample to avoid copy hidden cells.