- Jul 16, 2011
Private Sub Product_Exceptions() Call Remove_Filter 'Loans Call Field10_Delete_Filter_Rows(Criteria1) 'Deposits Call Remove_Filter Call Field10_Delete_Filter_Rows(Criteria2) Call Remove_Filter End Sub Private Sub Field10_Delete_Filter_Rows(Criteria As String) Worksheets(WkShM).Range("A1").AutoFilter Field:=10, Criteria1:=Criteria Application.DisplayAlerts = False ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete Application.DisplayAlerts = True End Sub Private Sub Remove_Filter() If Worksheets(WkShM).AutoFilterMode Then Worksheets(WkShM).Cells.AutoFilter End If End Sub
I am using the above code to Filter Data and delete the rows, so in the Sub Product_Exceptions, I first remove the Filter in the defined Worksheet WkShM if Autofilter is already on.
I then run Field10_Delete_Filter_Rows this procedure using the Defined Criteria (Criteria1)
I then repeat for a second Criteria, Criteria2.
I have around 10 Criteria I want to run through in this way.
The problem I am facing is that if the Filter for any of the Criteria comes up with no rows then this line
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
is deleting all my data it seems, so this technique only works if every Criteria I run through provides at least 1 filtered row.
Can anyone advise a way to stop this issue I describe above?
Thanks a lot!