Excel VBA: How to Use Target Row (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to initiate VBA Editor to use excel target row in Excel

  • 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.

How to insert new Module

  • 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.

Inserting Code for Event Handlers


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.

Showing Dataset Overview


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

Vba code to count selected row numbers.

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.

MsgBox showing Target Row number.


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 to change the interior color of target row.

🔎 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.

Changing interior color when condition is satisfied.

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.

Final output while changing interior color.


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.

Dataset Overview to delete target row.

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

Vba code to delete target row with condition.

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.

Showing an image when the first row is deleted.

Now select all other cells with marks.

Doing so, all the rows with marks less than 70 will be deleted.

Final result when all the relevant rows are 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.

Dataset overview to copy and paste target row if the condition is met.

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

Excel Vba code to copy and paste target row.

 🔎 VBA Code Breakdown

 If Target.Cells.Count = 1 Then

This code will work if you select only one cell.

 Range(Cells(x, y - 2), Cells(x, y)).Copy
 Range("F5:H5").PasteSpecial xlPasteValues

Upon satisfying the condition, the code copies the row and pastes to another location.

 Else: Range("F5:H5").ClearContents

If 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.

Final output when the target row has been copied and pasted.


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 to find target address in MsgBox.

🔎 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.

Showing target address with MsgBox.

Now, let’s Select the whole dataset.

And the MsgBox shows the Target Address.

Another target address shown in MsgBox.


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo