PDA

View Full Version : [SOLVED] If filtered rows are Yellow then delete else ignore and remove filter



shan
11-30-2015, 09:16 PM
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
12-01-2015, 05:29 AM
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
12-01-2015, 08:18 PM
Thank you for your reply. Code is working fine! Gr8 !!

mancubus
12-02-2015, 01:43 AM
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