[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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
0
castlerj
Asked:
castlerj
  • 2
  • 2
1 Solution
 
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
 
VipulKadiaCommented:
@castlerj : Yes, you can access with worksheet name also instead of index.
Yes. Even columns also you can assign dynamically.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now