Macro Breaks When criteria is deleted

  • Thread starter Thread starter tpeter
  • Start date Start date
T

tpeter

This code works just fine based off of a command button. The problem is when
you have criteria in the cells and the data is filtered, if you delete one of
the criteria options while it is filtered and try to filter it again, the
macro breaks. I have made this work by using a filter and unfilter button but
my curiosty has been spiked. The code is specific in saying that cells
b2,c2,and d2 must all be blank. I am not sure what error handling might help
this issue. Thank you for your help.
If IsEmpty(Range("b2")) And IsEmpty(Range("c2")) And IsEmpty(Range("d2"))
Then
ActiveSheet.ShowAllData
Else
Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("B1:D2"), Unique:=False
End If
Click to expand...

Tim Peter
 
I dont find an issue . I tried with the below code.
--Row13 and Row 1 are with headers (exactly same)
--Data starts at Row 13 which is the filter range..

Sub Macro()
If Trim(Range("b2") & Range("c2") & Range("d2")) = "" Then
If ActiveSheet.FilterMode = True Then _
ActiveSheet.ShowAllData
Else
Range("A13:D25").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("B1:D2"), Unique:=False
End If
End Sub

If this post helps click Yes
 
I mean...

Sub Macro()
If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
Range("A13:D25").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("B1:D2"), Unique:=False
End Sub

If this post helps click Yes
 
Thanks Jacob, I put it into my code and it doesn't break. The only issue is I
think there is so much data it just takes forever to sort it. If you hit
escape and end the code everything is sorted. I was just curious and
appreciate your help.

Tim
 
Back
Top