You can use Target Row in Excel VBA for different purposes. This is a useful tool to carry out many tasks by just selecting a specific row. In this article, you will get a proper idea of how you can use this Target Row for some practical purposes.
The below video illustrates the use of Target Row to delete an entire row. Here, we have deleted all those rows that have marks less than 70 and no color by selecting Target Row.
What Is Target Row in Excel VBA?
In Excel VBA Target Row means the row that a user has selected. This is a part of VBA Event Handlers. If we select any row, the associated code in the sheet will run automatically. This method is useful when we need to perform an action based on selected rows.
Target is a parameter of Event Handlers such as the Worksheet_SelectioChange Event. This Target parameter represents the range that the user has selected.
We can use the parameter with Row. For that, we have to use Target.Rows.
Benefits of Using Target Row in Excel VBA
There are some common benefits of using Target Row in Excel VBA. These benefits are:
- Data Entry: Target Row can be used for efficient data entry, saving time and energy.
- Data Validation: You can validate data using Target Row. This will alert the user if they try to input any invalid data.
- Calculation: Target Row can help you to perform dynamic calculations.
- Sorting and Filtering: It is convenient to use Target Row for the Sorting and Filtering process. The user just needs to select the data range and the dataset will be sorted instantly.
- Formatting: We can also Format data with this Target Row feature. The selected range will be automatically formatted.
How to Launch VBA Editor in Excel
To launch the VBA Editor in Excel, follow these steps:
- Open Microsoft Excel.
- Then, click on the Developer tab in the ribbon and click on the Visual Basic icon in the Code group.
- This will open the VBA Editor Window, in which the Project Explorer will be on the left-hand side and the Code Editor in the main area. Now, Select Module from the Insert tab.
- A new Module will be created where you can Write or Edit your VBA code in the Code Editor window.
Note: By default, the Developer tab remains hidden. In that case, you have to enable the Developer tab. To do this, go to File > Options > Customize Ribbon and then check the box next to Developer in the right-hand pane.
How to Insert VBA Code for Event Handlers
Inserting VBA code for Event Handlers is different from the normal procedure.
You have to insert the code on the same Sheet as the one you will take as a worksheet. Suppose you will perform Event Handlers in Sheet2. Then, you have to insert the code in Sheet2 in the Microsoft Excel Objects folder. You don’t need to run the code. When you change anything in the workbook, the code will run automatically.
Excel VBA to Use Target Row: 4 Practical Uses
We will use the Event Handler Worksheet_SelectionChange to use Target.Rows for different practical uses of Target Row in Excel VBA.
For that, we have taken this same dataset as previously shown.
1. Count Selected Row Number with Target Row
We can count the number of selected rows with Target.Rows. For this, we just need to use this simple code.
Copy the following code into the Sheet that you want to apply it to.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Long
x = Target.Rows.Count
'show in the MsgBox
MsgBox x
End Sub
This code will run when you select any range in the worksheet. It counts the number of selected rows and assigns them as x. Then the value of x is shown in the MsgBox.
Now, go to the worksheet and select the range B5:D19.
Doing so, a MsgBox will appear showing the number of selected rows.
2. Change Interior Color with Target Row
We can also change the interior color with Target Row. For this, we will use the same dataset as previously shown.
Also, we will change the interior color if the Mark of the student is equal to or greater than 70.
For this procedure, copy the following code into the same Sheet as that of the dataset.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x, y As Integer
x = Target.Row
y = Target.Column
'change interior color if condition is satisfied
If Target.Value >= 70 Then
Range(Cells(x, y - 2), Cells(x, y)).Interior.Color = RGB(144, 238, 144)
End If
End Sub
🔎 VBA Code Breakdown
This code will run when you select any range in the worksheet.
x = Target.Row
y = Target.Column- The values of x and y are respectively the row and column number of the selected cell.
If Target.Value >= 70 Then
   Range(Cells(x, y - 2), Cells(x, y)).Interior.Color = RGB(144, 238, 144)
   End If- If the selected cell value is greater or equal to 70, then with the cells and range properties, the row is highlighted.
Now, go to your dataset and select the D6 cell.
Doing so, the range B6:D6 will be highlighted, as shown in the below image.
Now, select every cell in the Marks column. Those with marks less than 70 will remain unchanged. But all the rows that have marks equal to or greater than 70 will be highlighted, as shown in the image below.
3. Delete Entire Row with Target Row
Now, let’s see how we can delete an entire row with Target.Rows. We have the same dataset as previously used. We have kept the highlighted rows. Furthermore, we want to delete all those rows that have marks less than 70 and no color.
For this procedure, copy the following code into the same Sheet as the one that has the dataset.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x, y As Integer
x = Target.Row
y = Target.Column
'delete row if condition is satisfied
If Target.Value < 70 Then
Range(Cells(x, y - 2), Cells(x, y)).EntireRow.Delete
End If
End Sub
This code checks the condition with an If loop and, upon meeting the condition, deletes the entire row.
Now select the D5 cell. As the marks in this cell are less than 70, the entire row will be deleted.
Now select all other cells with marks.
Doing so, all the rows with marks less than 70 will be deleted.
Note: There are two consecutive rows (S006, S007) that have marks of less than 70. If you select marks of S006, the entire row will be deleted, but the row of S007 will take its place and will remain selected. To solve this problem, you should first select the marks’ cell of the last row, and then you should select each cell upward.
4. Copy and Paste Entire Row Using Target Row
In this procedure, we will Copy Row values with some conditions and then paste them in another location.
We have taken the same dataset with a Result Box. We will use a code that will check the marks. If the marks are equal to or greater than 70, it will copy the row in F5:H5. Otherwise, the code will do nothing.
Copy the following code in the same Sheet that has your dataset.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
Dim x As Long
Dim y As Long
x = Target.Row
y = Target.Column
'Copy Row if Condition is Met and Paste in a Specific Range
If Target.Value >= 70 Then
Range(Cells(x, y - 2), Cells(x, y)).Copy
Range("F5:H5").PasteSpecial xlPasteValues
Else: Range("F5:H5").ClearContents
End If
End If
End Sub
 🔎 VBA Code Breakdown
 If Target.Cells.Count = 1 ThenThis code will work if you select only one cell.
 Range(Cells(x, y - 2), Cells(x, y)).Copy
 Range("F5:H5").PasteSpecial xlPasteValuesUpon satisfying the condition, the code copies the row and pastes to another location.
 Else: Range("F5:H5").ClearContentsIf the condition is not met, the code clears the contents in the result box.
Now, go to the worksheet and select D7.
As the mark is more than 70, the Code will copy the entire row and will paste it into the Result Box.
How to Find Range Address with Target.Address in Excel VBA
We can find the address of any Target row easily with the usage of Target.Address. This also can be done with Event Handlers. In this procedure, if we select any range the program will show the Target Address in a MsgBox.
Copy the following code in the same Sheet that has the dataset.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As String
'set x as Target Address
x = Target.Address
'show Target Address in MsgBox
MsgBox x
End Sub
🔎 VBA Code Breakdown
This code assigns Target Address to x and then shows it in the MsgBox.
Now, Go to your worksheet and Select any cell, say B5.
The MsgBox will show the Target Address.
Now, let’s Select the whole dataset.
And the MsgBox shows the Target Address.
Frequently Asked Questions
- How do I target the last row in Excel VBA?
When we have to find the last row, we can use different methods. The most common one is to use the End(XLDown) method.
- How do I select rows of selected cells in Excel VBA?
You can select rows of selected cells in Excel VBA. Say the B5 cell is already selected. You can select the total row by pressing Shift + Space.
- How do I select all rows with a certain value in Excel VBA?
To manually select all the data in a column, select the first cell, and press CTRL+SHIFT+DOWN ARROW. Likewise, to manually select a row and all columns attached to the row, press CTRL+SHIFT+DOWN ARROW+RIGHT ARROW.
Things to Remember
- The codes we used for Target Row have to be inserted in the Sheet. And a code will only work if the worksheet also has that particular code.
- The Event Handlers are very sensitive. When you don’t want the macro to run, make sure you have commented the code properly.
- You should select the range properly. If the code wants you to select only one cell, you have to select one cell. If you select multiple cells, the code will not work.
Download Practice Workbook
You can download and practice this workbook.
Conclusion
We have shown you 4 ways to use Excel VBA to use Target Row. You can use these methods in practical problems. We hope you find the content of this article useful. If there are further queries or suggestions feel free to mention them in the comment section.
