ajay_gajree
Well-known Member
- Joined
- Jul 16, 2011
- Messages
- 518
Code:
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
Hi All
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!