Contional Row Delete on Interior Color

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Newbie looking for some help..
I have a spreadsheet of unknown row length. I have a macro which uses various logic statements to format, sort and qualify row items. Problem rows contain cells formatted in a yellow color when suspicious data appears

I need a method to delete rows which have only all white interior cells formatting, thus leaving any row with at least one yellow formatted cell.

I use the following technique for most of the conditional actions

Dim s As Lon
With Worksheets("Jobs"
For s = .UsedRange.Rows.Count To 2 Step -
If .Cells(s, "B").Value <> "#104" Then .Rows(s).Delet
Nex
End Wit

Any suggestions are appreciated
Thanks
 
Alright Newb,
Here’s the deal. With “UsedRange.Rows.Count” you can only count on i
giving you the correct result if there are no empty rows at the top o
your worksheet. Instead, use this:

.UsedRange.Row - 1 + .UsedRange.Rows.Count

That will always work.

So try this, but be sure to change the "10" in "For c = 1 To 10" t
the number of columns you're concerned with:
With Worksheets(“Jobs”)
x = .UsedRange.Row - 1 + .UsedRange.Rows.Count
For r = x To 2 Step -1
keep = False
For c = 1 To 10
If .Cells(r, c).Interior.ColorIndex = 6 Then
keep = True
End If
Next c
If keep = False Then
.Rows(r).Delete
End If
Next r
End Wit
 
Back
Top