SORT/REMOVE ROWS EXCEL TABLE BASED ON BACKGROUND COLOR

I have a table that contains specific jobs and the number of times a person does that job.  Some of the people have done a job more than normal and those cells are highlighted.  I would like to remove those rows were there is no background color.  Attached is a sample of the table. EE-SORT-QUESTION.xls EE-SORT-QUESTION.xls
castlerjSenior AnalystAsked:
Who is Participating?
 
VipulKadiaCommented:
@castlerj : Yes, you can access with worksheet name also instead of index.
Yes. Even columns also you can assign dynamically.
0
 
StephenJRCommented:
Do you mean rows 11, 13, 14 etc?
0
 
castlerjSenior AnalystAuthor Commented:
Rows 8, 9, 11, 13, 14, etc...those with no values or background color.
0
 
VipulKadiaCommented:
See attached file. Macro is written in Module1.
Also same macro is given below :

Sub RemoveUnformattedRows()
On Error Resume Next

    Dim ShObj As Worksheet
    Dim lngFirstRow As Long
    Dim lngLastRow As Long
    Dim lngFirstCol As Long
    Dim lngLastCol As Long
    Dim lngRowIndex As Long
    Dim lngColorIndex As Long
   
    Set ShObj = ThisWorkbook.Worksheets(1)
    lngFirstRow = 2
    lngLastRow = ShObj.Cells(lngFirstRow, 1).End(xlDown).Row
    lngFirstCol = 3
    lngLastCol = 8
   
    For lngRowIndex = lngLastRow To lngFirstRow Step -1
        lngColorIndex = ShObj.Range(ShObj.Cells(lngRowIndex, lngFirstCol), ShObj.Cells(lngRowIndex, lngLastCol)).Interior.ColorIndex
        If Err.Number = 0 Then
            ShObj.Rows(lngRowIndex).Delete
        End If
        Err.Number = 0
    Next
   
End Sub

EE-SORT-QUESTION-1-.xls
0
 
castlerjSenior AnalystAuthor Commented:
Thanks!  It works great!  Before I close the question, can this be applied to worksheets with specific names and can it the column range be set/adjusted without counts the number of columns (code is set for a specific column range 3 to 8).
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.