Delete any row WITHOUT a fill color

aorum

New Member
Joined
May 12, 2011
Messages
3
-My spreadsheet has data in the following columns/rows A7:K25000 & N7:W25000
-Conditional formatting identifies integers 80 or greater and turns them yellow or read (depending on the number)(not the font, but the background)
-All rows are evaluated by conditional formatting. 80-90 = yellow 90+=red
-If just one cell on a row IS shaded (>80) then the entire row is to be left alone. If ALL cells on a row are (<80) NOT shaded then the entire row is to be delete.

I need to delete any row that is completely without a background. The would be all the way from column A to Column W.

Thanks in advance for the professional and well intentioned responses!

HERE IS AN EXAMPLE OF MY SPREADSHEET.
4/1/2011 0:01 =COLUMN A
9.98 =COLUMN B
0.82 =COLUMN C
11.77 =COLUMN D

4/1/2011 0:01 9.98 0.82 11.77 <<-Need this row deleted AND moved up (because no color)
4/1/2011 0:03 1.91 1.67 0.53 <<-Need this row deleted AND moved up (because no color)
4/1/2011 0:04 0.25 0.48 <<-Need this row deleted AND moved up (because no color)
4/1/2011 0:06 8.68 0.93 8.27 <<-Need this row deleted AND moved up (because no color)
4/1/2011 0:08 92.32 90.17 88.28 <<--80+ which would be yellow/red (DONT DELETE ROW!)
4/1/2011 0:09 0.33 0.80 <<-Need this row deleted AND moved up (because no color)
4/1/2011 0:11 11.2 80.7 015.74 <<--80+ which would be yellow (DONT DELETE ROW!)
4/1/2011 0:13 2.27 1.23 0.19<<-Need this row deleted AND moved up (because no color)
4/1/2011 0:14 0.23 0.38 <<-Need this row deleted AND moved up (because no color)
4/1/2011 0:16 10.2 60.6 12.54 <<-Need this row deleted AND moved up (because no color)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try this on a copy of your workbook:
Code:
Sub RemoveRows()
Dim r1 As Range
Dim dRws As Range
Dim ct1 As Long
Dim cols As Long

Set r1 = Range("B7:W25000")
cols = r1.Columns.Count + 1

For i = 1 To r1.Rows.Count
    ct1 = WorksheetFunction.CountIf(r1.Rows(i), ">80")
    If ct1 < 1 Then
        If dRws Is Nothing Then
            Set dRws = r1.Rows(i).Offset(0, -1).Resize(, cols)
        Else
            Set dRws = Union(dRws, r1.Rows(i).Offset(0, -1).Resize(, cols))
        End If
    End If
Next i
Application.ScreenUpdating = False
If dRws Is Nothing Then
    MsgBox "No rows deleted"
Else
    dRws.Delete
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,560
Messages
6,179,520
Members
452,921
Latest member
BBQKING

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
Back
Top