Then I am at a loss as to why the code I posted did not work... I just tested it here and it works as expected for me... as long as the cells in Column A are truly blank with nothing in them, the code I posted deleted their rows along with the colored cells' rows. Any chance you can post a copy of your workbook (with its original data from before you tried my code) to a file sharing facility like OneDrive, DropBox, etc. so I can watch my code at work "live"?
Okay, that is interesting... your worksheet is acting like it has something in those blank cells when there appears to be nothing in them. Sometimes when data is copied, cells that originally had "" in them come across without the "", but it is still there (as a null string I think). Anyway, the way to clear the problem is to assign the value of the cell back upon itself. Doing that for your data make my code work correctly. Here is the modified code for you to try...
[table="width: 500"]
[tr]
[td]Sub DeleteRowsContainingGrayCells()
Application.ScreenUpdating = False
Application.FindFormat.Clear
Application.FindFormat.Interior.Color = RGB(192, 192, 192)
With Columns("E")
With Intersect(.Cells, ActiveSheet.UsedRange)
.Value = .Value
End With
.Cells.Replace "*", "#N/A", SearchFormat:=True, ReplaceFormat:=False
On Error Resume Next
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End With
Application.FindFormat.Clear
Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]Looks like a post I just responded to, but the OP wants to delete everything except the RGB(192,192,192) filled cells. Rick, why not replace the formatted cells content with "" (instead of #N/A) and do a single specialcells call for xlBlanks?
@JoeMo,
[table="width: 500"]
[tr]
[td]Sub DeleteRowsContainingGrayCells()
Application.ScreenUpdating = False
Application.FindFormat.Clear
Application.FindFormat.Interior.Color = RGB(192, 192, 192)
With Columns("E")
With Intersect(.Cells, ActiveSheet.UsedRange)
.Value = .Value
End With
.Cells.Replace "*", "", SearchFormat:=True, ReplaceFormat:=False
On Error Resume Next
.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End With
Application.FindFormat.Clear
Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]@JoeMo,
Well... because.... let's see.... I mean.... well, it is like this... uh... I got nothing!
That is a great idea that I simply did not think of.
@ldashev,
This is what JoeMo meant...
Code:[TABLE="width: 500"] <tbody>[TR] [TD]Sub DeleteRowsContainingGrayCells() Application.ScreenUpdating = False Application.FindFormat.Clear Application.FindFormat.Interior.Color = RGB(192, 192, 192) With Columns("E") With Intersect(.Cells, ActiveSheet.UsedRange) .Value = .Value End With .Cells.Replace "*", "", SearchFormat:=True, ReplaceFormat:=False On Error Resume Next .SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0 End With Application.FindFormat.Clear Application.ScreenUpdating = True End Sub[/TD] [/TR] </tbody>[/TABLE]
