When you have some cells, columns or rows hidden in Microsoft Excel from Office 2007 or 2003and you try to select or copy only the visible rows or columns but even the hidden rows or columns are selected and copied. (Note: When the data has been filtered using filter tool, it won’t select the filtered cells, rows or columns). There is a hidden tool in the Excel which allows selecting only visible cells, rows or columns. This command can be added to the toolbar by customizing it.
So how to select only visible cells, or in other words how to not select hidden cells, rows or columns while copying?
In MS Excel 2007, to add the Select Visible Cells Command, do the following steps:
Step 1: Right click on the toolbar on top and click Customize Quick Access toolbar.
Step 2: Under Choose Command From drop down menu, select Commands Not in the Ribbon.
Step 3: Scroll down and click on Select Visible Cells.
Step 4: Click on Add and OK.
Now you should be able to see the command in the quick access toolbar.
(If you are interested in this black color layout for Office 2007, read from How to Change the Color Scheme or layout for Office 2007?)
In MS Excel 2003, to add the Select Visible Cells Command, do the following steps:
Step 1: Right click on the toolbar on top and click Customize.
Step 2: Go to Commands tab and select Edit under Categories.
Step 3: Scroll down the list under Commands and click on Select Visible Cells.
Step 4: Now drag and drop the command to the toolbar.
How to use Select Visible Cells Command?
To use this command, hide all the cells, rows or columns that you want to hide. (You can do that by choosing the rows or columns -> Right Click and choose Hide or you can use the keyboard shortcut to hide rows or columns by Ctrl + 9).
Now select the area that you want to copy (If you want to copy the whole sheet, press Ctrl + A) -> click on Select Visible Cells command -> press Ctrl + C and you should be able to see the blinking mark around the hidden rows or columns. Do Ctrl + V where you want to paste. You should be able to note that it had copied only visible cells.
Update: One of the useful tip where you can use this feature is to delete empty rows in between in Excel.
Really helpful. Thanks
I had really hard time with this - Thanks a lot! Great solution!
Top notch! This is exactly what I needed!
In older Excel versions only the visible rows were selected by default…
Thanks for that spent a good part of the day tryinhg to sort this, Sorted………..
u r tips works
on small sheets
but when the no. of rows \
are more then 20000 then it shows
this messege
Thanks very much for this. Have been struggling with this one for a while, particularly since 2003 behaves differently
Great work. Thanks so much.