When we work with an Excel spreadsheet, sometimes some columns are becoming too wide to fit on the screen or maybe there are not important at that moment and make unnecessary clutter.
In the following example, there is a list of a few people.
If you hide one column and select the entire table, use copy, and then paste, the hidden rows or columns will also be copied. It doesn’t matter if you use Ctrl + A, Ctrl + Shift + End, or select values by dragging.
What you need to do is to select values inside the range and only the visible cells. You can do it using different methods.
Select Visible Cells using Go To Special
Let’s hide column C and drag values from A1 to D7.
Now, go to Home >> Editing >> Find & Select >> Go To Special. From the dialog box, select Visible cells only.
Click OK.
Now, if you look at the selection, the C column is not selected.
Copy cells and paste them to see that the column was indeed not selected.
Using Keyboard Shortcut
You can quickly select only visible cells using the Left Alt + ; keyboard shortcut for Windows and Cmd + Shift + Z for Mac on selected cells.
What about filtered rows?
If you are dealing with filtered rows that are not visible on the screen, you don’t have to worry about them as they are not selected by default.
Make a shortcut using Quick Access Toolbar
If you don’t want to remember these steps to access the dialog window or memorize the keyboard shortcut, you can create a shortcut on Quick Access Toolbar.
If your QAT is hidden, you must show it first. To do it, right-click any part of the ribbon and select Show Quick Access Toolbar.
This is the part that should appear:
If you can’t see it, look at the top of the ribbon as you can change its position.
Right-click the QAT and choose Customize Quick Access Toolbar….
From Excel Options, select All Commands and then add Select Visible Cells.
If you click OK, a new icon on the Quick Access Toolbar appears.
Now, you can select cells, click this button and all the visible cells inside the selected area will be selected.
Create a macro to select visible cells
The problem with adding a shortcut to QAT is that you must select cells first. This time we are going to create a Macro that will select only visible cells inside the whole current range with the data.
Click inside data on the spreadsheet and go to View >> Macros >> Macros >> Record Macro…. You can also create a keyboard shortcut here.
Press OK to start recording.
Click Ctrl + A to select the current region and then Left Alt + ; to select only visible cells.
Sub select_visible() ' ' select_visible Macro ' ' Keyboard Shortcut: Ctrl+Shift+Q ' Range("A1:D7").Select Range("B5").Activate Selection.SpecialCells(xlCellTypeVisible).Select End SubRun the macro to select the desired range. The problem with this macro is that it works only for this specific example because the exact range is written into the code.
What we want to do, is to select the current region (the region with an active cell).
Let’s replace the two first lines on the procedure with the line selecting the current region. The full procedure looks like this:
Sub select_visible() ' ' select_visible Macro ' ' Keyboard Shortcut: Ctrl+Shift+Q ' ActiveCell.CurrentRegion.Select Selection.SpecialCells(xlCellTypeVisible).Select End SubYou can also add this Macro as a shortcut to QAT.
This time instead of selecting All Commands, select Macros.
Macros added to a toolbar have long and not the best-looking name, also the icon is the standard Macro icon.
We can easily change that by clicking the Modify… button. Here you can change the name and icon.
Click OK and the shortcut will appear on the Quick Access Toolbar.
Click a range of data with hidden rows or columns and notice that the whole region is selected with only visible data.