Delete Unhighlighted rows

I am looking for a solution that will delete all rows of data that are not highlighted.

I have my sheet attached for reference. Thanks so much!
sample.xls
zyanjAsked:
Who is Participating?
 
ragnarok89Commented:
Oops, had it backwards. THIS one deletes NON-Highlighted rows:

Sub Macro2()

r = 2
While Cells(r, 1).Value <> ""
    If Cells(r, 1).Interior.ColorIndex = xlNone Then
        Rows(r).Delete
    Else
        r = r + 1
    End If
Wend
End Sub

Open in new window

0
 
jppintoCommented:
Here's how you could do it:

Create a UDF like this:

Function GetInteriorColor(ByVal Target As Range) As Integer
    GetInteriorColor = Target.Interior.ColorIndex
End Function

For that, you need to insert it on a VBA Module.

Then on column F you could use this function on a formula like this:

=IF(GetInteriorColor(E8)=19,"Filled","")

This will write "Filled" on each row that has the cell on column E with a yellow fill. The you can had a header to this column and create an AutoFilter to select only the rows that have Filled on column F. Just select the rows and delete them.

Please take a look at the attached example.

jppinto
sample-1-.xls
0
 
jppintoCommented:
Instead of this "...to select only the rows that have Filled on column F"

I meant this:

...to select only the rows that don't have "Filled" on column F (=Blanks).

Sorry for the mistake...

jppinto
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
zyanjAuthor Commented:
? This is what I get on your sheet?
Capture.JPG
0
 
krishnakrkcCommented:
Hi,

try this


Kris
Sub kTest()
    Dim dic As Object, ka, k(), i As Long, n As Long, Concat As String
    
    ka = Range("a1").CurrentRegion
    Set dic = CreateObject("scripting.dictionary")
        dic.comparemode = 1
        For i = 2 To UBound(ka, 1)
            Concat = ka(i, 2) & "|" & ka(i, 3)
            dic.Item(Concat) = dic.Item(Concat) + 1
        Next
    ReDim k(1 To dic.Count, 1 To UBound(ka, 2))
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 2 To UBound(ka, 1)
            Concat = ka(i, 2) & "|" & ka(i, 3)
            If dic.Item(Concat) > 1 Then
                n = n + 1
                For c = 1 To UBound(ka, 2)
                    k(n, c) = ka(i, c)
                Next
            End If
        Next
    End With
    If n Then
        With Range("a1")
            .CurrentRegion.Offset(1).ClearContents
            .Offset(1).Resize(n, UBound(ka, 2)).Value = k
        End With
    End If
End Sub

Open in new window

0
 
krishnakrkcCommented:
Hi,

Ignore above code. Try this

Kris
Sub kTest()
    Dim dic As Object, ka, k(), i As Long, n As Long, Concat As String
    
    ka = Range("a1").CurrentRegion
    Set dic = CreateObject("scripting.dictionary")
        dic.comparemode = 1
        For i = 2 To UBound(ka, 1)
            Concat = ka(i, 2) & "|" & ka(i, 3)
            dic.Item(Concat) = dic.Item(Concat) + 1
        Next
    ReDim k(1 To dic.Count, 1 To UBound(ka, 2))
    For i = 2 To UBound(ka, 1)
        Concat = ka(i, 2) & "|" & ka(i, 3)
        If dic.Item(Concat) > 1 Then
            n = n + 1
            For c = 1 To UBound(ka, 2)
                k(n, c) = ka(i, c)
            Next
        End If
    Next
    If n Then
        With Range("a1")
            .CurrentRegion.Offset(1).ClearContents
            .Offset(1).Resize(n, UBound(ka, 2)).Value = k
        End With
    End If
End Sub

Open in new window

0
 
jppintoCommented:
Please check the attached file...I think that I posted the file without saving the complete version :)

See if this is working...
sample-1-.xls
0
 
ragnarok89Commented:
This works, and it's simple:

Sub Macro2()

r = 2
While Cells(r, 1).Value <> ""
    If Cells(r, 1).Interior.ColorIndex = xlNone Then
        r = r + 1
    Else
        Rows(r).Delete
    End If
Wend
End Sub

Open in new window

0
 
zyanjAuthor Commented:
Simple and exactly what I was looking for.

Thanks a lot !!!!!!!!!!!!!!!1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.