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, Intersect in our VBA script. So, let’s be with us through the entire article.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
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
- A new subroutine named For_Next is introduced in the first line.
- The variables i, lastRow, and nRange are declared and initialized in the next lines. lastRow will store the row number of the last cell containing data in Column F, and nRange is a range object.
- The Cells method and the End(xlUp) function are used in the following line of code to locate the last row containing data in Column F. This method is frequently used to identify the last row in a range.
- Then For…Next loop initializes. It starts at Row 4 and iterates up to the last row found in step 3. We used the loop variable i to keep track of the current row.
- The If…Then condition determines whether “Delivered” appears in Column F of the current row. If it does, the statement’s code will be executed.
- The Set statement creates a new range object for columns B to F of the current row if nRange is empty. The Union statement merges the current row’s range with any existing range objects in nRange if it already does so.
- The program verifies if nRange holds any cells once the loop has been completed. If this occurs, the Select method is used to choose the range. If not, a message box informing the user that no rows containing “Delivered” were found is displayed.
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.
Read More: How to Select Columns with VBA (3 Easy Ways)
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
- This code will search in the range F4:F14 for the first instance of “Delivered” using the Find method. The search should look for an exact match, according to the instructions in the xlValues and xlWhole parameters. Then, we set rng named range to the cell containing “Delivered” if it is discovered.
- The script then verifies if rng is not Nothing. If it is true, then the code has discovered “Delivered” inside the range.
- The code initializes the new range nRange to cover only columns B through F by intersecting it with the range B:F if it has found “Delivered“. We did this by setting the EntireRow property to the full row where the code found “Delivered“. Then, it will change the current cell to the cell below where it found “Delivered“.
- Following this, the code starts a loop that iterates through all cells below the one where it detected “Delivered” until it reaches the last cell in the range F14.
- The code examines whether the current cell includes “Delivered” for each cell in the loop. If it does, it uses the Union function and the Intersect method to include only columns B through F when adding the full row to the new range nRange.
- The code selects the new range nRange, which contains all rows with “Delivered” after the loop has finished.
- It’s a good idea to have a backup plan in case something goes wrong, but it’s not always possible.
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.
Read More: Excel VBA: Select All Cells with Data
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: How to Select Cell with VBA in Excel (6 Useful Ways)
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 hightlight 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.
Read More: How to Select Range Based on Cell Value VBA (7 Ways)
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.
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.