How to Remove Highlighted Rows in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

Easy Ways to Remove Highlighted Rows in Excel


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.

Utilizing Filter Command From Home Tab to Remove Highlighted Rows in Excel

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

Applying VBA Code to Remove Highlighted Rows in Excel

  • 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Bishawajit Chakraborty
Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo