Excel Select Visible Cells VBA
In Excel select visible cells only or filtered range, by avoiding hidden rows or columns, using any of these tips.
- Copy visible cells only VBA
- Excel copy visible cells only
- (a) Keyboard Shortcut to Copy only visible cells.
- (b) Menu option
The first one is for Excel programmers to automate this process. The other options are Manual methods.
1. Excel Copy Visible Cells Only VBA
Add a auto-filter over a column or a table, Select a value in the filter. This will hide some rows.
To select & copy only these visible cells, after applying a filter, the conventional method Ctrl+A, Ctrl+C will not work out. Use this VBA or the Keyboard shortcuts explained.
This Vba will copy only visible cells. It has 2 routines.
Copy visible Cells only VBA code in ExcelVisual Basic123456789101112131415161718192021222324Option 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
- First will just select all visible cells in the active sheet.
- Second routine will copy non-hidden cells in a specific range.
For the second routine, pass the range as parameter. Only the visible cells in this range will be copied to the destination sheet.
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 above code & the following manual methods avoid this issue.
2.a Keyboard Shortcut to Copy visible Range 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 to use “Alt + Semicolon” before using out copy shortcut. This option will also copy only the visible cells in your current view.
2.b 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 You skip any of the steps or order is changed, the hidden rows will be also get copied & pasted. So, be careful while following the steps.
Copy All Cells & Copy Visible Cells
Sometimes We use Copy + Paste in Excel to select visible cells, just like how we use it to select all cells.
After pasting we will realize that we don’t need all the data. You will go back to source sheet & reconfirm your 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. If your sheet has thousands of records, master these tiny Tips. It will save a lot of your productive time.
Previous Post: 7 Amazing Things to Say to Siri
Next Post: 5 Amazing Productivity Tools That Extends Your Free Time
Copy Visible Cells Only VBA – Excel Macro Code – Keyboard shortcut