delete all rows EXCEPT rows with highlighted cells
 

Hi all,

I have a massive spreadsheet - over 38,000 lines. Some lines have highlighted cells, some don't (the highlights are errors in the data that I have found). I need to take out all of the rows that do not have any highlighted cells in them (the "correct" rows) so I am left with only rows with data that has cells highlighted (the "incorrect" rows).

I am 'meh' at excel, you know, can do some simple conditional formatting, computations, pivot tables, etc, but am definitely not a coder... go easy on me :)

Thanks!!

delete all rows EXCEPT rows with highlighted cells
 

Hi Renata,

Am Wed, 22 Jun 2016 11:35:19 -0700 (PDT) schrieb
:

I have a massive spreadsheet - over 38,000 lines. Some lines have highlighted cells, some don't (the highlights are errors in the data that I have found). I need to take out all of the rows that do not have any highlighted cells in them (the "correct" rows) so I am left with only rows with data that has cells highlighted (the "incorrect" rows).

press Alt+F11 = Insert = Module and paste the following code into the
code window. Then go back to the spreadsheet and run the macro (Test it
in a copy of your worksheet):

Sub DeleteRows()
Dim LRow As Long, i As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = LRow To 1 Step -1
If Rows(i).Interior.ColorIndex < vbNull Then
Rows(i).Delete
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016