Excel Select Visible Cells VBA
In Excel select visible cells only, by avoiding hidden rows or columns, make use of any of these tips.
- Excel copy visible cells only
- Menu option
- Keyboard Shortcut
- Copy visible cells only VBA
When we use a filter over a column or a table, some rows will become hidden. Those rows will not be visible to the user. If we want to pick all the rows that are visible after applying a filter, the conventional method will not work out.
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. Lets see how to avoid this.
1.a Excel Copy Visible Cells only using Menu option
MS Excel has built in option to copy & paste visible cells only. They have added this option to the Excel menu itself for ease of use. After applying a filter, carefully follow the steps explained below one by one. (Application to MS Excel 2007 & above).
- Select the range of cells that needs to be copied.
- Choose Excel ‘Home’ Menu.
- In ‘Editing Tab’, click “Find & Select” option.
- Click on “Go to Special”.
- Choose “Visible cells only” & click Ok.
- Press ‘Ctrl + C’.
- Select target location or sheet.
- Press ‘Ctrl + P’.
Now, you can see only the visible cells are being pasted. Yes. It is bit a lengthy process for first time. Once if you know that the trick is in the “Find & Select” menu option “Go to Special”, then it is all easy after that.
If any of the steps are not followed in order, then even the hidden rows will be pasted. So, be careful while following the steps.
1.b Keyboard Shortcut to Copy visible Cells only in Excel
The keyboard shortcut by passes the need to use the menu option and also, comparatively it involves only few steps as listed.
- Select rage of cell to be copied.
- Press “Alt + ;” (Alt + Semicolon).
- Press “Ctrl + C”
- Select target location cell.
- Press “Ctrl + P”
Just remember that “Alt + Semicolon” has to be used before using out Copy shortcut. This option will also copy only the visible cells in your current view.
2. Excel Copy Visible Cells Only VBA
To explain this, we have 2 routines here. First will just select all visible cells in the active sheet. Second routine will copy non-hidden cells in a specific range.
123456789101112131415161718192021222324Option ExplicitSub Copy_Visible_Cells_Only()'Select only the visible cells in active sheet'Code from Officetricks.com website.Cells.SelectSelection.SpecialCells(xlCellTypeVisible).Select'Since only the visible cells are selected. Only they are copied and pasted to target cellSelection.CopySheets("Sheet2").SelectActiveSheet.PasteEnd SubSub Select_Visible_Cells_In_Range(Rng As Range)'Select visible cells in the chosen RangeRng.SelectSelection.SpecialCells(xlCellTypeVisible).Select'Copy Visible cells only in the range and paste in target sheetSelection.CopySheets("Sheet2").SelectActiveSheet.PasteEnd Sub
For the second routine, pass the range as parameter. Only the visible cells in this range will be copied to the destination sheet.
While doing Copy + Paste in Excel, if unexpected data gets pasted, you will go and reconfirm your copy selection area again. Every Excel Novice would have come across this problem where you might’ve overlooked the hidden rows/columns. WYSIWYG(What You See Is What You Get) logic will go wrong here.
Many times you have to undo & clear the filters and need to do selective copy/paste which is manual intensive work. And if your sheet had thousands of records, then it is time to master these tiny Tips.
Previous Post: 7 Amazing Things to Say to Siri
Next Post: 5 Amazing Productivity Tools That Extends Your Free Time
- Excel copy visible cells only
How to Copy Visible Cells Only in Excel using VBA ?