How to Select All Cells with Data in Excel (5 Methods)

Method 1 – Select All Cells with Values Using the ‘Go To Special’ Command

The dataset below has data on several areas of fruit sales date-wise. However, it has some blank cells. We will select all the non-contiguous cells with data using Excel’s ‘Go To Special’ command.

Select All Cells with Values Using ‘Go To Special’ Command in Excel

Steps:

Select the entire dataset (B4:E14).

From Excel Ribbon, go to Home > Editing > Find & Select > Go To Special.

As a result, the ‘Go To Special‘ dialog box will appear.
Choose the Constants option and press OK.

Once you press OK, Excel will highlight all the cells with data by default.

Method 2 – Excel VBA to Select All Cells with Data

Steps:

Go to the worksheet that contains the dataset.
Right-click on the corresponding sheet name and select View Code.

The VBA window will open up.
Type the code below in the Module and run the code using the F5 key. Or you can use the Run Sub/UserForm icon to run the code.

Sub SelectwithData()
Dim abc As Range
Dim WorkRange As Range
Dim OutRange As Range
On Error Resume Next
xTitleId = "Select Cells with Data"
Set WorkRange = Application.Selection
Set WorkRange = Application.InputBox("DataRange", xTitleId, WorkRange.Address, Type:=8)
For Each abc In WorkRange
    If abc.Value <> "" Then
        If OutRange Is Nothing Then
            Set OutRange = abc
        Else
            Set OutRange = Union(OutRange, abc)
        End If
    End If
Next
If Not OutRange Is Nothing Then
    OutRange.Select
End If
End Sub

Upon running the code, the following message box will pop up.
Enter the dataset range and press OK.

You will see that all the cells that have data are highlighted below.

Method 3 – Select All Cells with Specific Data Using Excel Find Option

Steps:

Select any cell in the sheet where you have the dataset.

Press Ctrl + F to bring up the Find and Replace dialog box.
From the Find tab, type ‘Apple’ in the Find what field.
Press Find All.

Clicking on Find All will give you the list of cell references with ‘Apple’ in them.
Press Ctrl + A to select the entire list.
You will see all the cells with ‘Apple’ highlighted when the result of Find All is selected.