Excel VBA Select Row Based on Cell Value (3 Ways)

Users may quickly and simply execute operations on enormous amounts of information using VBA that would otherwise take a lot of time and be prone to error when done manually. Excel power users and programmers use VBA to carry out tasks like reporting and data analysis that require handling massive volumes of data. In this reference, we’ll discuss various methods to select row in Excel based on cell value using VBA, along with recommended practices and potential problems to watch out for.

Here, we’ll show how you can perform the same task with different approaches; different codes indeed. We’ll exhibit the For…Next, Do…Loop loops, along with different methods like Union, and Intersect in our VBA script. So, let’s be with us through the entire article.

How to Launch Visual Basic Editor in Excel

The Developer tab contains the VBA environment, including tools for creating and recording macros, Excel Add-ins, Forms controls, importing and exporting XML data, etc. This is our starting point for running VBA codes; just follow along.

Note: By default, the Developer tab remains hidden. You can enable the Developer tab in Excel.

• Once enabled, move to the Developer tab, then click on the Visual Basic icon in the Code group.

• This launches the Microsoft Visual Basic for Applications window. Now, click on the Insert tab and choose Module from the list. Afterward, we get a small module window to insert our VBA code, and we hit the Run button or F5 key to execute the code.

The advantage of inserting the code in the module is that we can apply this code to all the worksheets in this Excel workbook. Conversely, we can make our code only available for a specific worksheet.

Excel VBA Select Row Based on Cell Value : 3 Examples

For ease of understanding, we are going to use a Product Delivery Status Report of a particular grocery store. This dataset includes the Name, Date, Product, Qty, and Status in columns B, C, D, E and F respectively.

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

Now, we’ll utilize this dataset to select row based on cell value using VBA in Excel. So, let’s explore them one by one.

Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

1. Utilizing For…Next Statement to Select Row Based on Cell Value

In the above image, we have selected the rows that have “Delivered” status in the F column. We’ll use the VBA macro to accomplish this task. In the code, we used the For…Next loop, If statement, and VBA Union function to select row based on cell value in Excel. After running the code, all the desired rows (rows 5, 8, 9, 12, 14) get selected at the same time.

Below is the code we used to do this.

Sub For_Next()
' Declare variables
Dim i As Long, lastRow As Long
Dim nRange As Range
' Find the last row in column F
lastRow = Cells(Rows.Count, "F").End(xlUp).Row
' Loop through each row from row 4 to the last row
For i = 4 To lastRow
' Check if the cell in column F contains "Delivered"
If Cells(i, "F").Value = "Delivered" Then
' If nRange hasn't been set yet, set it to the current row
If nRange Is Nothing Then
Set nRange = Range("B" & i & ":F" & i)
Else ' If nRange already has a range, add the current row to it
Set nRange = Union(nRange, Range("B" & i & ":F" & i))
End If
End If
Next i
' If nRange has been set, select it
If Not nRange Is Nothing Then
nRange.Select
Else ' If no rows containing "Delivered" were found, display a message box
MsgBox "No rows contain 'Delivered' in column F."
End If
End Sub

From the above video, we can easily see that whenever we execute the code, all the rows with “Delivered” status in column F get selected in the blink of an eye. Actually, selecting rows one by one is quite an easy task. But showing all rows selected at once is a bit more complex than this. Because, if we gain help from just looping, it’ll select a row, and at the next iteration it will select the new row which fulfills the condition. But, at that time, the previously selected row will get deselected. For selecting all preferred rows at once, we took the help of the VBA Union function.

Overall, this code looks for any rows in a worksheet’s column F with the value “Delivered” in them. For each of those rows, it picks the range from columns B to F.

1.1 Selecting Rows by Quantity Criteria

Have you noticed that here we selected rows containing Delivered and Not Delivered both? Can you tell us why this happened? Let’s think about it… Did you get it? Okay, no problem.

Actually, here, our criteria are totally different. We selected rows that have sales quantities greater than 20. So, while keeping this condition, we must select rows containing both statuses. Follow the steps given below to select row based on quantity criteria as cell value using VBA in Excel.

Following is the working code for this criteria.

Sub Qty_Criteria()
' Define variables
Dim i As Long, lastRow As Long
Dim selectedRange As Range
' Find the last row in column E (assumes there are no blank cells in the column)
lastRow = Cells(Rows.Count, "F").End(xlUp).Row
' Loop through each row from row 4 to the last row
For i = 5 To lastRow
' Check if the Qty is greater than 20
If Cells(i, "E").Value > 20 Then
' Add the row to the selectedRange
If selectedRange Is Nothing Then
Set selectedRange = Rows(i)
Else
Set selectedRange = Union(selectedRange, Rows(i))
End If
End If
Next i
' Select the selectedRange if it is not Nothing
If Not selectedRange Is Nothing Then
selectedRange.Select
Else
MsgBox "No rows meet the specified criteria."
End If
End Sub

From the visual demonstration provided, we can understand that this code selects rows that have a quantity greater than 20 in Column E. In the code, you observed that we started the loop from Row 5 as the previous one is the heading row of the data table. So, we omitted it from our iteration. As a result, rows 6, 7, and 9 are selected using the declared criteria.

You can see that the first row with data in the data table isn’t selected. Because this customer made an order of 20 mangoes, which is less than the condition. If we change our criteria in the code and alter the (>) sign with a (=>) sign, then this row will also be added to the selection. You can try this in our own workbook and let us know in the comment section.

1.2 Selecting Rows by Date Criteria

Here, we carried out the same work with different criteria. We selected rows having orders for March month. So, the rows, from 7 to 14, are selected altogether. These all have dates in march in column C. The code is almost identical to the previous method. So why did we add it as a separate method? The shortest answer is to show how the same code can be used in different contexts.

Follow the steps given below to select row based on date criteria as cell value using VBA in Excel.

Here is the functioning code for this method:

Sub Date_Criteria()
' Define variables
Dim i As Long, lastRow As Long
Dim selectedRange As Range
' Find the last row in column E (assumes there are no blank cells in the column)
lastRow = Cells(Rows.Count, "F").End(xlUp).Row
' Loop through each row from row 5 to the last row
For i = 5 To lastRow
' Check if the Date is in March
If Month(Cells(i, "C").Value) = 3 Then
' Add the row to the selectedRange
If selectedRange Is Nothing Then
Set selectedRange = Rows(i)
Else
Set selectedRange = Union(selectedRange, Rows(i))
End If
End If
Next i
' Select the selectedRange if it is not Nothing
If Not selectedRange Is Nothing Then
selectedRange.Select
Else
MsgBox "No rows meet the specified criteria."
End If
End Sub

For clear understanding, read the comments in the code carefully. I hope this will make sense completely.

Observing the video, we can see that there is another way to execute a code from the module. Without clicking on the Run icon in the ribbon, we can make the code run using the Macro dialog box.

• Just click on the Macros option in the Developer tab to open this dialog box. Alternatively, you can press F8 on your keyboard to mimic the same task. Just find the macro you created and click on the Run button in the dialog box.

After executing the code, all the rows, including the purchase orders from March, get selected at once. Generally, this code may be applied to different Excel spreadsheets and is useful for selecting rows based on a certain criterion, such as a specified date range or other parameters. The main benefit is that we can modify this code according to our/users demands. You just need to alter the condition inside the If…Else statement carefully.

2. Using Do…Loop Statement to Select Row Based on Cell Value

In this method, the output is fully similar to Method-1. The only difference is in the code. Here, we used a different loop: Do…Until loop. Also, we utilized another function named the VBA Intersect function with the Union function. Other things are the same. A message box will show the message “No rows contain “Delivered” in column F” if it’s not found in the defined range here too. Let’s get to the code part.

The following code exemplifies the workings of this method:

Sub Do_Loop()
' Declare variables
Dim rng As Range, cl As Range, nRange As Range
' Find the first occurrence of "Delivered" in column F
Set rng = Range("F4:F14").Find("Delivered", , xlValues, xlWhole)
' Check if "Delivered" was found
If Not rng Is Nothing Then
    ' If found, initialize the new range to the entire row where "Delivered" was found
    Set nRange = Intersect(rng.EntireRow, Range("B:F"))
    ' Set the current cell to the cell below where "Delivered" was found
    Set cl = rng.Offset(1)
    ' Loop through each cell below where "Delivered" was found
    Do Until cl.Row > Range("F14").Row
        ' Check if the current cell contains "Delivered"
        If cl.Value = "Delivered" Then
            ' If it does, add the entire row to the new range
            Set nRange = Union(nRange, Intersect(cl.EntireRow, Range("B:F")))
        End If
        ' Move to the next row
        Set cl = cl.Offset(1)
    Loop
    ' Select the new range containing all rows with "Delivered"
    nRange.Select
Else
    ' If "Delivered" was not found, display a message box
    MsgBox "No rows contain 'Delivered' in column F."
End If
End Sub

By watching the video, we can discern that the code is running perfectly and returning the correct output to us. It selected all rows with “Delivered” status at once. If there are no rows with this status, the code will return a message box with negative tidings.

Ultimately, this code illustrates how to use the Find method to search for a specific value in a range, how to use a Do Until loop to iterate through a range’s cells, and how to merge ranges using the Union and Intersect methods. It can be modified to be used in different Excel files to choose rows based on particular requirements.

3. Selecting Rows That Have Data in First Column

This image illustrates that there are some blank cells in the first column (Column B).

Now, we will show you how to select row based on blank cell values using VBA in Excel. Here, our plan is to omit those rows which have blank cells in the first column. Ultimately, we selected those rows having values in the first columns only. Otherwise, we will neglect it.

You can see the code for this method below:

Sub Select_Rows_With_Data()
Dim lastRow As Long
Dim i As Long
Dim selectedRows As Range
' Get the last row in the range
lastRow = Range("B4:F14").Rows.Count + 3
' Loop through each row in the range
For i = 5 To lastRow
' Check if column B in the row has data
If Range("B" & i).Value <> "" Then
' Add the current row to the selectedRows range
If selectedRows Is Nothing Then
Set selectedRows = Rows(i)
Else
Set selectedRows = Union(selectedRows, Rows(i))
End If
End If
Next i
' Select the rows and display a message box
If Not selectedRows Is Nothing Then
selectedRows.Select
Else
MsgBox "No rows found with data in column B.", _
vbExclamation
End If
End Sub

After executing the code, we can see that it has selected the first two rows, but the third one isn’t. Because it doesn’t have any value in cell B7. That’s why, according to our condition in the code, it gets omitted. Also, we started the loop from 5 to ignore the heading row from our code script.

Read More: Excel VBA to Select Last Row

How to Highlight Row Based on Cell Value Using VBA in Excel

The difference here with the previous methods is that now we are highlighting the entire row instead of just selecting them. We gave a light background fill color to these rows that have “Delivered” status in Column F. This code is actually easier than the previous ones. Because highlighting is quite simpler than selecting them all at once.

The code provided below is the functional implementation of this method:

Sub Highlight_Row()
Dim i As Long, lastRow As Long' Find the last row in column E
lastRow = Cells(Rows.Count, "F").End(xlUp).Row' Loop through each row from row 4 to the last row
For i = 5 To lastRow
' Check if the cell in column E contains "Delivered"
If Cells(i, "F").Value = "Delivered" Then
' Highlight the row with yellow color
Range("B" & i & ":F" & i).Interior.ColorIndex = 20
End If
Next i
End Sub

We can do it another way. That approach is more complex than the declared one. Then, why do we want to show it? Because it’s a modified version of the code we used in Method 2. We have to change just a property of that code. Take a look below.

Sub Highlight_Change_Property()
' Define variables
Dim rng As Range, cl As Range, nRange As Range
' Find the first cell that contains the value "Delivered" in column F (range F4:F14)
Set rng = Range("F4:F14").Find("Delivered", , xlValues, xlWhole)
' If a cell containing "Delivered" is found
If Not rng Is Nothing Then
' Define a range (nRange) that includes the entire row of the found cell, but only columns B:F
Set nRange = Intersect(rng.EntireRow, Range("B:F"))
' Define a variable for the cell directly below the found cell
Set cl = rng.Offset(1)
' Loop through the remaining cells in column F (down to F14)
Do Until cl.Row > Range("F14").Row
' If a cell contains "Delivered", add the entire row (but only columns B:F) to the nRange range
If cl.Value = "Delivered" Then
Set nRange = Union(nRange, _
Intersect(cl.EntireRow, Range("B:F")))
End If
' Move to the next cell below
Set cl = cl.Offset(1)
Loop
' Highlight the entire nRange range in a light color
nRange.Interior.Color = RGB(254, 253, 195)
Else
' If no cells containing "Delivered" are found, display a message box
MsgBox "No rows contain 'Delivered' in column F."
End If
End Sub

Go through the above code carefully, and you’ll find that at the line just after the Do…Until loop, we change the property of the range named nRange from Select to Interior.Color. With this little change, we highlighted the rows with a color from the proclaimed RGB code.

It’s clear from the video that while running the code, it will highlight the rows with the “Delivered” status with a light background color (pastel yellow). Our advise is to use the first code we showed in this method to highlight. Because the second one is complicated and unnecessary apparently for this task. But if you used this before while just selecting rows and don’t want to write a new code to highlight rows, then you can take advantage of the second one.

How to Extract Row Based on Cell Value Using VBA in Excel

Sometimes you need to extract some specific rows from a mixed information table. At this time, this example could be helpful. Here, the range B4:F14 is our main range. From this range, we extracted rows with “Delivered” status. After obtaining the rows, we copied and pasted them into a new range (B16:F21) just below the source range.

The following code exemplifies the workings of this method:

Sub Extract_Row()
' Define variables
Dim i As Long, lastRow As Long
Dim nRange As Range, exRange As Range
' Find the last row in column E (assumes there are no blank cells in the column)
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
' Loop through each row from row 4 to the last row
For i = 4 To lastRow
    ' Check if the cell in column F contains "Delivered"
    If Cells(i, "F").Value = "Delivered" Then
        ' If the nRange range hasn't been set yet, set it to the current row
        If nRange Is Nothing Then
            Set nRange = Range("B" & i & ":F" & i)
        Else ' If the nRange range already has a range, add the current row to it
            Set nRange = Union(nRange, Range("B" & i & ":F" & i))
        End If
    End If
Next i
'Creating heading of the extracted range
With Range("B" & lastRow + 2 & ":F" & lastRow + 2)
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Interior.Color = RGB(237, 240, 194)
    .Value = "Extracted Data"
    .Font.Bold = True
    .Font.Italic = True
    .BorderAround LineStyle:=xlContinuous, Weight:=xlThin
End With
' If the nRange range has been set (i.e. rows containing "Delivered" were found)
If Not nRange Is Nothing Then
    ' Define a range (exRange) that starts below the last row of the data in columns B:F and ends with the same number of rows as nRange
    Set exRange = Range("B" & lastRow + 3 & ":F" & lastRow + 3 + nRange.Rows.Count - 1)
    ' Copy the nRange range and paste it into the exRange range
    nRange.Copy exRange
Else ' If no rows containing "Delivered" were found, display a message box
    MsgBox "No rows contain 'Delivered' in column F."
End If
End Sub

Follow the comments to explore the intents of the code.

The video illustrates that at starting there was just the source range visible in the worksheet. After executing the code, it will extract rows with the preferred status and make themselves accommodated in a range below one row of the main data table. If you want to run the code again, make sure to delete the range with extracted data first.

How to Delete All Other Rows Based on Cell Value with VBA

By watching the image, we can discern that running the code in the picture made an effect in the data range. The data table looks smaller than our original dataset. Because some rows are missing in it. If we see closely, there are just rows available with “Delivered” status. So, our code deleted all other rows except the “Delivered” ones. It’s also a simple task to accomplish.

This is the working code for the method:

Sub Delete_Other_Rows()
' Define variables
Dim i As Long, lastRow As Long
Dim nRange As Range
' Find the last row in column F (assumes there are no blank cells in the column)
lastRow = Cells(Rows.Count, "F").End(xlUp).Row
' Loop through each row from the last row to row 5 (going backwards)
For i = lastRow To 5 Step -1
' Check if the cell in column F does not contain "Delivered"
If Cells(i, "F").Value <> "Delivered" Then
' Delete the row if it doesn't contain "Delivered" status
Rows(i).Delete
End If
Next i
End Sub

At first, there is the original dataset available in our worksheet. But whenever we run the code, it will remove all rows except those with “Delivered” status from the sheet. In other words, it will delete rows with a “Not Delivered” status. And after it deletes the row, the row below it comes up and fills the empty space.

How to Highlight Rows with Different Colors Based on Multiple Criteria Without VBA in Excel

We set a Conditional Formatting rule for the table such that it will highlight rows with values greater than 20 in the ‘Qty‘ column with a light green background color, and rows with values less than 10 in the ‘Qty‘ column with a light gold background color. Any rows in the table that do not meet either of the criteria specified will retain their original formatting and will not have a fill color applied to their background.

• At first, we must select the data range where we need to apply the Conditional Formatting. Then, select Home >> Conditional Formatting >> New Rule options in this mentioned sequence.

• Instantly, the New Formatting Rule dialog box will appear. Here, click on Use a formula to determine which cells to format and write the below formula in the box:

• Next, click on the Format button.

• In the Format Cells dialog box, you’ll find the Fill tab, where you can select your desired background color. We chose a color as shown in the above image and clicked OK. It’ll return you to the New Formatting Rule dialog box again. There, just click on the OK button again to apply the formatting and exit the dialog box.

In a similar way as before, add a new rule for those rows with a value of quantity less than 10 and a light gold background color.

Magically, you can see that these two rules are working together in the worksheet. Not to mention, we have applied them to this worksheet only. So, there is no effect of these rules on other sheets. Now, if we change the value of the quantity in any row, if it meets the criteria, the row color will automatically change. Suppose you changed the order quantity of David from 20 to 25. What will be the fill color of Row 5? Let us know in the comment section.

Frequently Asked Questions

• What happens if there are numerous cells in the range that have the same value?

All the methods in our reference cover this idea. We have several rows with the same value in a respective column, and we selected all those rows which have the specific value in that particular column.

• Is it possible to select a row using multiple cell values?

You can change the code to choose a row depending on a variety of cell values. Using numerous If statements to verify each column in the row for the desired values is one approach to accomplishing this. Otherwise, you can use the AND statement to do that as well. In Method 1.1 and Method 1.2, we showed how we can select rows by quantity criteria and date criteria in two different sheets. But, can you do it in a single method? So, you have to write a code to select rows that have Quantity greater than 20 and which Dates are in March month. If you can, let us know in the comment section.

• Is it possible to select a range of cells based on a cell value?

It’s kind of the same as our work. You can use a similar piece of code to select a range of cells depending on a cell value. You would choose the range of cells that contain the corresponding value rather than choosing the full row. Also, You may accomplish this by changing the Row property in the code to the Range property.

• If the value you’re looking for is not inside the range, what should you do?

The code won’t select any rows if the value you’re looking for isn’t available in the range. You can incorporate an error handler that informs the user that the code did not find the value to deal with this situation. In our codes, we added a part to show a message box with the message “No rows contain “Delivered” in Column F” with the Else part of the If statement.

• Can I use this code with for a filtered range?

Yes, you can use this code for a filtered range. But you must make sure that you have included both visible and hidden cells in the search range. You can accomplish this by either utilizing the complete column range for the search or the SpecialCells method in VBA to pick only visible cells.

Things to Remember

• Your data set’s size will determine whether you need to optimize the code to boost performance. You can achieve this by reducing the number of times you enter the worksheet and removing unnecessary calculations or loops.
• Always, select the range before applying Conditional Formatting. It sets the range of applications.
• Make sure to put an equal sign “=” before the formula in the New Formatting Rule dialog box. Otherwise, it won’t work.
• Remember that, if you delete rows with VBA code, you cannot undo this action and make them visible again by just pressing CTRL + Z.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

VBA offers a robust collection of tools for automating Excel activities, including the ability to select row based on cell value in particular columns. Find and select the desired rows, you can do this by using loops, conditional statements, range objects, and some of their methods and properties. It works well with a large amount of data where manual selection takes a lot of time. When using VBA to alter data in Excel, you must add caution because mistakes can result in data loss or corruption. Because of this, it’s crucial to test and validate any VBA code before using it on sensitive data. However, don’t forget to share your suggestions in the comments section below.