Sometimes you may need to select an entire row based on any specific data in a cell of that row. You can do the task in multiple ways. In this article, I’ll show you 4 easy and simple ways to select a row in Excel if a cell contains specific data.
How to Select Row in Excel If Cell Contains Specific Data: 4 Simple Ways
For demonstration purposes, I have a dataset of the Owners of different books. Now, I’ll show you how to select rows if a cell contains specific data by selecting the entire rows for a specific Owner.
1. Utilize Filter Feature to Select Row Based on Specific Data in Excel
The easiest way to select rows based on the specific data of a cell is by using the Filter feature.
- Firstly, select the entire dataset and go to Home > Editing > Sort & Filter > Filter.
- After that, we will see small downward arrows appear beside the row headers.
- Then, click on the arrow beside the Owner.
- Simultaneously, it will open a dropdown menu.
- Moreover, select Harold from this dropdown menu and click on OK.
- Finally, you will see only the rows that contain Harold.
- Lastly, select those rows manually by pressing Ctrl first.
Read More: How to Select Specific Rows in Excel Formula
2. Select Row If Cell Contains Specific Text Using Conditional Formatting
You can also select rows based on specific data in a cell by using Conditional Formatting.
- Firstly, select your entire dataset and go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
- Consecutively, we will see a window named Text That Contains opened.
- Then, type the data based on which row will be selected in the box Format cells that contain the text.
- For this dataset, I’ve typed Ana.
- Also, in the with section, select your preferred formatting styles and press OK. I’ve selected Light Red Fill with Dark Red Text.
- Finally, you will see the cells which contain the data highlighted.
- In addition, you can select the rows by pressing CTRL and clicking on the row numbers of the highlighted cells.
3. Applying Excel Find & Select Features
Find & Select features can also be used to select the rows if a cell contains specific data.
- Firstly, select your entire dataset and go to Home > Editing > Find & Select > Find.
- Then, you will see a window named Find and Replace opened.
- Further, type the data based on which rows will be selected in the Find what box.
- For this dataset, I’ve typed Harold.
- After that, click on Find All.
- After that, you will see the cells that have that specific data shown at the bottom of the Find and Replace window.
- Later on, select the cells and close the Find and Replace window.
- As a result, you will see the cells which contain the Harold selected.
- Also, you can do it for the owner Ana obviously.
- Finally, select the entire rows by pressing CTRL and clicking on the row numbers of the cells.
4. Incorporating VBA to Select Row
Using Visual Basic for Applications (VBA), you can easily select the rows if a cell in those rows contains specific data.
- Firstly, press ALT+F11 to open the VBA window
- After that, from the left panel of this window, right-click on the sheet name and go to Insert > Module.
- Simultaneously, It will open the Module (Code) window.
- Afterward, insert the following code in this Module (Code) window
Sub select_rows_with_given_data()
Dim Rng As Range
Dim myCell As Object
Dim myUnion As Range
Set Rng = Selection
searchdata = InputBox("Please Enter the Search data")
For Each myCell In Rng
If InStr(myCell.Text, searchdata) Then
If Not myUnion Is Nothing Then
Set myUnion = Union(myUnion, myCell.EntireRow)
Else
Set myUnion = myCell.EntireRow
End If
End If
Next
If myUnion Is Nothing Then
MsgBox "The data was not found in the selection"
Else
myUnion.Select
End If
End Sub
- Afterward, close the VBA window, select your dataset, and go to View > Macro to run the Macro.
- Instantly, a window named Macro will be opened.
- Then, select select_rows_with_given_data from the Macro name box and click on Run.
- Instantly, it will open a custom box.
Later on, In the Please Enter the Search data box type the specific data and click on OK.
- After running the macro, the code will open a custom box where you can insert the data. If the data is found in your selected cell ranges, the macro will select the entire row. If the data is not found, it will give an error message.
- Finally, you will see all the rows that contain the specific data in one of its cells are selected.
Download Practice Workbook
Conclusion
You can select a row in Excel if a cell contains specific data by following any of the above-described methods. Please leave a comment if you have any confusion.
