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..
- VBA: Copy visible cells only
- 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.
- First example will just select all visible cells in the active sheet.
- Second routine will copy non-hidden cells in a specific range.
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
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.