If filtered rows are Yellow then delete else ignore and remove filter

[shan]

Hello Everybody,

I have a macro which is filtering data on Yellow color and deleting the rows... but the problem is that if there are no yellow rows, code is deleting all the rows available.

Can anybody suggest a solution that if after filter yellow rows are available then delete else remove filter.

Code I have:
ActiveSheet.UsedRange.AutoFilter Field:=8, Criteria1:=RGB(255, _
255, 0), Operator:=xlFilterCellColor

Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True


ActiveSheet.ShowAllData

Thanks

Regards,
Shan

[nilem]

try this

With ActiveSheet
    .UsedRange.AutoFilter Field:=8, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
    If .AutoFilter.Range.Columns(1).SpecialCells(12).Count > 1 Then _
       .UsedRange.Offset(1, 0).Resize(.UsedRange.Rows.Count - 1).EntireRow.Delete
    .ShowAllData
End With

[shan]

Thank you for your reply. Code is working fine! Gr8 !!

[mancubus]

very similar to nilem's code.
but you don't need to test if there are rows that meet the autofilter criteria as it deletes the visible (excludes row 1 via offset function) rows only..

Sub vbax_54413_Delete_AutoFilter_Rows()
    With ActiveSheet
         .AutoFilterMode = False
         .Cells(1).AutoFilter Field:=8, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
         .UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Delete
         .AutoFilterMode = False
     End With
End Sub