Do you want to select only the visible cells in a range from Excel?

Excel allows you to hide, filter, and group rows of data. But what happens when you only want to select the visible cells when these features are used?

For certain types of visible cells, selecting a range will select all of the range including the hidden cells. This might not be what you intend.

For example, a common requirement is to copy and paste only the visible values from a range. This can be done in a few ways.

This post will explore the various methods of selecting only the visible cells.

Select Visible Cells with a Keyboard Shortcut

Here is a small dataset of cars. You can see a few of the rows have been hidden. You can see this from the row headings, certain numbers are missing from the sequence.

When you select the visible range of data to copy and paste, this will include all the hidden cells. You will first need to select only the visible cells.

You can select cells manually, but it will be tedious. A better option is to use one of Excel’s built-in methods for selecting the cells.

The easiest way to select visible cells is with a keyboard shortcut. Here’s how.

1. Select the full range of cells as you normally would do.
2. Press the Alt + ; keyboard shortcut.

Notice the active range green border will disappear and the cells will appear as a disjointed set of light grey cells to indicate only the visible cells are selected.

When you press Ctrl + C to copy the visible cells, this becomes even more obvious as the green dashed border appears around each set of visible cells.

Select Visible Cells with the Go To Special Menu

Another way to select only the visible cells in your sheet is with the Go To menu.

The Go To menu allows you to select only cells whose content or properties meet certain criteria such as cells that are blank, contain formulas, or have notes attached. This includes the ability to select only visible cells.

1. Select the full range of cells that contain the visible cells you want to select.
2. Go to the Home tab.
3. Click on the Find & Select command in the Editing section.
4. Select the Go To Special option from the menu.

5. Select the Visible cells only option from the Go To Special menu.
6. Press the OK button.

This will select only the visible cells from your original selection.

You can also access the Got To menu with a keyboard shortcut. Press Ctrl + G to open the Go To menu and then click on the Special button to open the Go To Special menu.

Select Visible Cells with the Quick Access Toolbar

Selecting only the visible cells to copy and paste might be an action you frequently use.

If this is the case, you might want to add this command to the quick access toolbar. This way, it will always be easily available with a single click.

This isn’t a command that you can find in the ribbon, but it is available to add to the toolbar.

Here’s how you can add this command to the quick access toolbar.

1. Right-click anywhere on the quick access toolbar.
2. Select the Customize Quick Access Toolbar option from the menu. This will open the Excel Options menu on the Customize Toolbar section where you will be able to add new commands.

Now you will be able to add the command to your toolbar.

3. Select the Commands Not in the Ribbon option.
4. Scroll down the list and click on the Select Visible Cells option.
5. Click on the Add button to add the command to your toolbar.
6. Press the OK button.

Now you should see the command in your toolbar. You can now use this to select visible cells.

7. Select the range of cells.
8. Click on the Select Visible Cells command in the toolbar.

Now the visible cells are selected!

Select Visible Cells with VBA

Automation with VBA might be a big part of your work process.

Selecting visible cells with VBA is also possible and will only require a single line of code.

Selection.SpecialCells(xlCellTypeVisible).Select

The above line of VBA code will select only the visible cells from the currently selected range.

Select Visible Cells with Office Scripts

If you are working with automating using Office Scripts, then you can also easily select visible cells with a line of code.

function main(workbook: ExcelScript.Workbook) {
  let rng = workbook.getSelectedRange();
  rng.getSpecialCells(ExcelScript.SpecialCellType.visible);
}

The above Office Script code will get the selected range from your sheet and then select the visible cells using the getSpecialCells method.

Conclusions

Selecting only the visible cells from a range is an important first step if you want to copy and paste values from a range that contain hidden rows or columns.

You can quickly reduce your selected range to only the visible cells with a shortcut, the Go To menu, or the quick acess toolbar.

Selecting visible cells can also be incorporated into your automation with either VBA or Office Scripts.

Did you know you could easily select the visible cells in a range? Let me know in the comments below!