Select visible cells only

This tutorial shows how to only select visible cells from a selected range using Excel or VBA

METHOD 1. Select visible cells only

EXCEL

Select a range > Home tab > Editing group > Click on Find & Select > Go To Special > Select Visible cells only > Click OK

This image represents the original data that has all of the cells visible.

This image shows the same data, however, row 4 is now hidden and is not visible. Therefore, if you select and try to copy or delete this information it will also delete the content in the hidden row.

1. Select the range, which has hidden cells.
Note: in this example row 3 has been hidden in the selected range.

Select range in which row has been hidden

2. Select the Home tab.

3. Click Find & Select in the Editing group.
4. Click Go To Special.

Click Find & Select and click Go To Special

5. Select Visible cells only in the Go To Special window..
6. Click OK.

This image shows the result of the process, which now only has the visible cell selected.

METHOD 1. Select visible cells only

VBA

Sub Select_only_visible_cells()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'select visible cells only in the selected range
ws.Range("B2:C6").SpecialCells(xlCellTypeVisible).Select

End Sub

ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet in which you want to apply a restriction to a cell by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Range: Select the range from which you only want to select visible cells by changing the range reference ("B2:C6") in the VBA code.

Explanation about how to select visible cells only

EXPLANATION

This tutorial shows how to only select visible cells from a selected range using Excel or VBA.

This tutorial provides one Excel method and one VBA method that can be applied to only select visible cells from a selected range. The Excel method uses the 'Visible cells only' option from the Go To Special menu. The VBA method uses the SpecialCells(xlCellTypeVisible) function to select visible cells only.