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
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. |
 |
| 2. Select the Home tab. |  |
| 3. Select Find & Select in the Editing group. 4. 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. |
 |
| 9. When you click the Find Next button it will select the first occurrence of the value in the active sheet. |  |
| 10. Click on the Find Next button again to select the next occurrence of the specific value in the active sheet. |  |
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
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
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
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
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.
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
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 |
|---|---|---|
| Find and select cells with specific value in a range | How to find and select cells with specific value in a range using Excel and VBA methods | |
| Find and Replace cells with specific value in a range | How to find and replace the cells with specific value in a selected range using Excel and VBA methods | |
| Find and Replace cells with specific value in a worksheet | How to find and replace the cells with specific value in an entire worksheet using Excel and VBA methods | |
| Find and select cells between specific values in a range | How to find and select cells between specific values in a range using Excel and VBA methods | |
| Fill blank cells with a specific value | How to fill blank cells with a specific value using Excel and VBA methods |