Macro Breaks When criteria is deleted
 

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

Tim Peter

Macro Breaks When criteria is deleted
 

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
---------------
Jacob Skaria


"tpeter" wrote:

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

Tim Peter

Macro Breaks When criteria is deleted
 

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"tpeter" wrote:

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

Tim Peter

Macro Breaks When criteria is deleted
 

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"tpeter" wrote:

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

Tim Peter