Find and select cells with specific value in a worksheet

How to find and select the cells with specific value in an entire worksheet using Excel and VBA methods

METHOD 1. Find and select cells with specific value in a worksheet

EXCEL

Select worksheet > Home tab > Editing group > Find & Select > Find > Enter value in Find what: input box > Select Sheet in the Within: input box > Click Find Next

1. Select the worksheet in which you want to find and select specific value.
Note: in this example we only want to search and select in Sheet1.
Select worksheet in which to Find and Select

2. Select the Home tab. Select Home tab - Excel 2016

3. Select Find & Select in the Editing group.
4. Click Find.
Select Find and Select and click Find

6. Enter the value that you want to find and select in the Find what: input box.
7. Select Sheet in the Within: input box.
8. Click Find Next.
Enter value select Sheet and click Find Next

9. When you click the Find Next button it will select the first occurrence of the value in the active sheet. First occurrence of value selected

10. Click on the Find Next button again to select the next occurrence of the specific value in the active sheet. Second occurrence of value selected

METHOD 1. Find and select cells with specific value in a worksheet using VBA

VBA

Sub Select_specific_value()
'declare variables
Dim ws As Worksheet
Dim SelectCells As Range
Dim xcell As Object
Set ws = Worksheets("Analysis")
'check each cell in the specific worksheet if the criteria is matching
For Each xcell In ws.UsedRange.Cells

If xcell.Value = 500 Then

If SelectCells Is Nothing Then
Set SelectCells = Range(xcell.Address)
Else
Set SelectCells = Union(SelectCells, Range(xcell.Address))
End If

End If

Next

'select the cells with specified value
SelectCells.Select

End Sub

ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet in which you want to select the cells with a specific value 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.
Specific Value: Select the value that you want to test for and select the cells if it equals to this value by changing the value of "500" in the VBA code.

Explanation about how to find and select cells with specific value in a worksheet

EXPLANATION

EXPLANATION

This tutorial shows how to find and select cells with specific value in a worksheet using Excel and VBA methods.
This tutorial provides one Excel method that can be applied to find and select cells with a specific value in an entire worksheet. This method only selects one cell at a time by clicking on the Find Next button after completing all the required steps. This does not select all of the cells that contain the specific value at once. To achieve this you need to apply the VBA method.
Using VBA you can find and select cells with a specific value, in an entire worksheet. By applying the VBA method it will select all of the cells that contain the specific value.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to find and select cells with specific value in a range using Excel and VBA methods
How to find and replace the cells with specific value in a selected range using Excel and VBA methods
How to find and replace the cells with specific value in an entire worksheet using Excel and VBA methods
How to find and select cells between specific values in a range using Excel and VBA methods
How to fill blank cells with a specific value using Excel and VBA methods