When working with large data sets in Microsoft Excel, you may need to remove specific rows from the worksheet for security purposes. This article will demonstrate how to remove highlighted rows in Excel in 2 easy ways.
Here, we will discuss how to remove highlighted rows in Excel by using the Filter command and applying VBA code. Let’s suppose we have a sample data set.
1. Utilizing Filter Command from Home Tab to Remove Highlighted Rows
In this section, we will show you how to use the Filter command to remove highlighted rows in Excel.
Steps:
- Here, we will select the entire data set.
- Firstly, go to the Home tab.
- Secondly, choose the Sort & Filter tool.
- Thirdly, click on the Filter command.
- Here, you can see the highlighted row in the below image.
- So, click on the Filter icon from the Employee Name column.
- So, if you want to remove the highlighted row, you have to unselect this item from the below image.
- Then, unselect the name “Jim”.
- After that, click OK.
- Finally, you can not find the highlighted row in the below image.
2. Applying VBA to Remove Highlighted Rows
VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate a VBA code that makes it very easy to remove highlighted rows in Excel.
Steps:
- Firstly, we will open the Developer tab.
- Then, we will select the Visual Basic command.
- Here, the Visual Basic window will open.
- After that, from the Insert option, we will choose the new Module to write a VBA code.
- Now, paste the following VBA code into the Module.
- Besides, to run the program, click the “Run” button or press F5.
Sub Delete_Highlighted_rows()
'Declaring Variables
Dim yRg As Range, Row As Range
'Using For Loop
For Each yRg In ThisWorkbook.ActiveSheet.Range("b4:b13")
'Setting interior color index
If yRg.Interior.ColorIndex = 24 Then
If Row Is Nothing Then
Set Row = yRg
Else
Set Row = Union(Row, yRg)
End If
End If
Next yRg
'Delete the row
If Not Row Is Nothing Then Row.EntireRow.Delete
End Sub
- Lastly, the given image shows the highlighted row is not displayed here.
Download Practice Workbook
You may download the following Excel workbook for better understanding.
Conclusion
In this article, we’ve covered 2 Ways to remove highlighted rows in Excel. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
Related Articles
- How to Delete All Rows Below a Certain Row in Excel?
- How to Delete Rows in Excel with Specific Text?
- How to Delete Blank Rows in Excel?
- How to Delete Row If Cell Contains Specific Values in Excel?
- How to Delete Row If Cell Is Blank in Excel?
- How to Delete Empty Rows at the Bottom in Excel?
- How to Delete All Rows Not Containing Certain Text in Excel?
- How to Delete Rows Based on Another List in Excel?
