VBA Delete all rows that don't contain a color

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
525
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows
Hi, I would like to delete rows where the rows don't contain any color, however I have a lot of data to delete so I what I want to do is step through the data and add the rows to an array/collection that don't contain any color then delete them all at once, currently I can do it one row at a time however it takes a long time to do. I can't filter on color as different cells in the rows may or may not contain color. I can have up to 10,0000 rows of data and could end up deleting 9,999 if no cells have any colour in them. can someone help please

The code I am using

VBA Code:
Sub DeleteNonColor()

Dim iCntr As Long
Dim Rng As Range
    Set Rng = ActiveSheet.UsedRange
    For iCntr = Rng.Column + Rng.Rows.count - 1 To Rng.row Step -1
        If Not Cells(iCntr, 1).EntireRow.Interior.ColorIndex = 19 Then Rows(iCntr).EntireRow.Delete
    Next

End Sub
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Code below assumes that your used range starts in cell A1 (ie that there is something in Row 1 and in column A
Test on a COPY of your data

Code below delete any row containing one or more cell whose ColorIndex is NOT 19
VBA Code:
Sub DeleteNonColor()
    Dim Cel As Range, Rng As Range, uRng As Range, c As Long, r As Long
    Set Rng = ActiveSheet.UsedRange
    Set uRng = Rng.Offset(Rng.Rows.Count).Resize(1, 1)
   
    For r = 2 To Rng.Rows.Count
        For c = 1 To Rng.Columns.Count
            Set Cel = Rng.Cells(r, c)
            If Not Cel.Interior.ColorIndex = 19 Then
                Set uRng = Union(uRng, Cel)
                Exit For
            End If
        Next c
    Next r
    uRng.EntireRow.Delete
End Sub

NOTE
uRng always contains at least one cell
Therefore, there is no requirement for the expected condition If Not uRng Is Nothing Then
 
Last edited:

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
Alternatively, try looping the columns and filter each one by NoFill. Then delete visible cells (entire row).
 
Last edited:

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
525
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows
Hi Yongle, the code deleted all cells even with color so I just added "EntireRow" to get the results required, many thanks for your help

VBA Code:
Sub DeleteNonColor()
    Dim Cel As Range, Rng As Range, uRng As Range, c As Long, r As Long
    Set Rng = ActiveSheet.UsedRange
    Set uRng = Rng.Offset(Rng.Rows.Count).Resize(1, 1)
   
    For r = 2 To Rng.Rows.Count
        For c = 1 To Rng.Columns.Count
            Set Cel = Rng.Cells(r, c)
            If Not Cel.EntireRow.Interior.ColorIndex = 19 Then
                Set uRng = Union(uRng, Cel)
                Exit For
            End If
        Next c
    Next r
    uRng.EntireRow.Delete
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Glad you worked it out (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,859
Messages
5,627,290
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top