Find and select cells with specific value in a range
How to find and select the cells with specific value in a selected range using Excel and VBA methods
Select range > 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 range from which you want to find and select specific value. Note: in this example we have selected range reference (B3:C9). |
 |
| 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 selected range. |  |
| 10. Click on the Find Next button again to select the next occurrence of the specific value in the selected range. |  |
METHOD 1. Find and select cells with specific value in a range using VBA
VBA
Sub Select_specific_value()
'declare variables
Dim ws As Worksheet
Dim SelectCells As Range
Dim xcell As Object
Dim Rng As Range
Dim ws As Worksheet
Dim SelectCells As Range
Dim xcell As Object
Dim Rng As Range
Set ws = Worksheets("Analysis")
Set Rng = ws.Range("B3:C9")
Set Rng = ws.Range("B3:C9")
'check each cell in a specific range if the criteria is matching
For Each xcell In Rng
For Each xcell In Rng
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 which captures the range of cells 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.
Range: Select the range that captures the cells in which you want to select the cells with a specific value by changing the range reference ("B3:C9") in the VBA code.
Worksheet Selection: Select the worksheet which captures the range of cells 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.
Range: Select the range that captures the cells in which you want to select the cells with a specific value by changing the range reference ("B3:C9") in the VBA code.
EXPLANATION
This tutorial shows how to find and select cells with specific value in a range 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 a selected range. 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 a specified range. 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 worksheet | How to find and select cells with specific value in a worksheet 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 |