Prantick
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 row in Excel if a cell contains specific data.
Here, I’ve 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 where the Owner is Harold.
Download Practice Workbook
4 Ways to Select Row in Excel If Cell Contains Specific Data
1. Filter to Select Row Based on Specific Data in Excel
The easiest way to select rows based on the specific data of a cell is using the Filter feature. First,
➤ Select the entire dataset and go to Home > Editing > Sort & Filter > Filter.
After that, small downward arrows will appear beside the row headers.
➤ Click on the arrow beside Owner.
It will open a dropdown menu.
➤ Select Harold from this dropdown menu and click on OK.
As a result, you will see only the rows which contain Harold are selected in the list.
Read More: How to Highlight Active Row in Excel (3 Methods)
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.
First,
➤ Select your entire dataset and go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
A window named Text That Contains will be opened. Now,
➤ 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 Harold.
➤ In the with box, select your preferred formatting styles and press OK. I’ve selected Light Red Fill with Dark Red Text.
As a result, you will see, the cells which contain the data will be highlighted.
Now, you can select the rows by pressing CTRL and clicking on the row numbers of the highlighted cells.
Read More: Highlight Row If Cell Contains Any Text
Similar Readings:
- How to Unhide Top Rows in Excel (7 Methods)
- Data Clean-up Techniques in Excel: Randomizing the Rows
- Hide Rows and Columns in Excel: Shortcut & Other Techniques
- Hidden Rows in Excel: How to Unhide or Delete Them?
- How to Freeze Rows in Excel (6 Easy Methods)
3. Excel Find & Select Features
Find & Select features can also be used to select the rows if a cell contains specific data.
First,
➤ Select your entire dataset and go to Home > Editing > Find & Select > Find.
Now a window named Find and Replace will be opened.
➤ 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.
Now, the cells which have that specific data will be shown at the bottom of the Find and Replace window.
➤ Select the cells and close the Find and Replace window.
As a result, you will see, the cells which contain the data will be selected.
➤ Select the entire rows by pressing CTRL and clicking on the row numbers of the cells.
Related Content: Excel Alternating Row Color with Conditional Formatting [Video]
4. Using VBA to Select Row
Using Microsoft Visual Basic Applications (VBA), you can easily select the rows if a cell in those rows contains specific data.
➤ First, press ALT+F11 to open the VBA window
➤ After that, from the right panel of this window, right click on the sheet name and go to Insert > Module.
It will open the Module(Code) window.
➤ 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 SubAfter 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 entire row will be selected. If the data is not found, an error message will be shown.
➤ Now, close the VBA window, select your dataset and go to View > Macro to run the Macro.
As a result, a window named Macro will be opened.
➤ Select select_rows_with_given_data from the Macro name box and click on Run.
It will open a custom box.
➤ In the Please Enter the Search data box type the specific data and click on OK.
As a result, you will see, all the rows which contain the specific data in one of its cells are selected.
Related Content: VBA to Hide Rows in Excel (14 Methods)
Conclusion
You can select 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.
