Delete entire row based on 2 criteria

**coopman64** · 11-13-2012, 03:11 PM

I'm trying to write a macro to delete an entire row only if all cells are blank and all cells have colorindex = 15. The sheet is 40 rows long and 10 columns wide, with some cells at the bottom of the list highlighted yellow. The problem I'm having is that it deletes rows until it reaches a point where all columns contain data. I need I need it to just delete the rows below the longest list.

**AlphaFrog** · 11-13-2012, 03:35 PM

Try this...

Sub Delete_Rows()
    
    Dim i As Long, cell As Range, counter As Long
    
    Application.ScreenUpdating = False
    For i = 40 To 1 Step -1
        With Range("A" & i & ":J" & i)
            If Application.WorksheetFunction.CountBlank(.Cells) = .Count Then
                counter = 0
                For Each cell In .Cells
                    If cell.Interior.ColorIndex <> 15 Then Exit For Else counter = counter + 1
                Next cell
                If .Count = counter Then Rows(i).Delete
            End If
        End With
    Next i
    Application.ScreenUpdating = True
    
End Sub