Delete Unhighlighted rows

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

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

? This is what I get on your sheet?
Capture.JPG

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

Select allOpen in new window

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

Select allOpen in new window

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

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

Select allOpen in new window

Simple and exactly what I was looking for.

Thanks a lot !!!!!!!!!!!!!!!1