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).

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

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

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

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

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

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.

Excel VBA to Select All Cells with Data

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

Excel VBA to Select All Cells with Data

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

Excel VBA to Select All Cells with Data

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.

Select All Cells with Specific Data Using Excel Find Option

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.

Select All Cells with Specific Data Using Excel Find Option

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.