Deleting rows if one cell in the row is not a specific color

dunk123 · Mar 5, 2011

Hi all

I have a bunch of customer data that is given to sales reps. Each customer account is on one row, with name, address etc in different columns. Using some basic testing principles, any missing or invalid data has its cell colored using:

Code:

With Selection.Interior
                .Color = 65535
            End With

I now wish to loop through all the rows, testing if any cells in the row are colored. If there are no cells in the entire row that are colored, then I wish to delete the row. I've conceptualized how to do it, but just not quite there.

Any help greatly appreciated. Many thanks.

VoG · Mar 5, 2011

Hi. Perhaps like this

Code:

Sub Test2()
Dim LR As Long, LC As Long, i As Long, j As Long, k As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For i = LR To 1 Step -1
    k = 0
    For j = 1 To LC
        If Cells(i, j).Interior.Color = 65535 Then
            k = 1
            Exit For
        End If
    Next j
    If k = 0 Then Rows(i).Delete
Next i
End Sub

dunk123 · Mar 5, 2011

Works like a dream. Awesome, thank you.

Deleting rows if one cell in the row is not a specific color

dunk123

Board Regular

Excel Facts

VoG

Legend

dunk123

Board Regular

Similar threads

Forum statistics

Share this page

Deleting rows if one cell in the row is not a specific color

dunk123

Board Regular

Excel Facts

VoG

Legend

dunk123

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock