Conditional Testing of 3 cells

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

tpeter

I have a large spreadsheet that runs a macro using the advance filter to
isolate data and change the carts respectivly. The criteria range is b2:d2,
the macro and filter works well as long as there is information in cell b2.
If I only use cells c2 and d2 to sort the information the code breaks. What I
am trying to accomplish is if cells b2:d2 are all blank then showalldata, if
there is one of these cells that has information in it sort by that
information. Here is some of the codes that I have tryed:

If (IsEmpty("b2") And IsEmpty("C2") And
IsEmpty("D2")) Then
Call ShowAll
Else
Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("B1:D2"), Unique:=False
End If

If Range ("b2:d2") = "" Then
Call ShowAll
Else
Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("B1:D2"), Unique:=False
End If

Thank you for your help.

Tim Peter
 
you still need to use RANGE

If (IsEmpty("b2") And IsEmpty("C2") And

should be
If IsEmpty(Range("b2")) And IsEmpty(Range("C2")) And ....


elseif .... and so on
 
Patrick,

Thank you for your response. The code is only recognizing B2. If this cell
is populated then all 3 criteria will filter. If I only use cells c2 and d2
(leaving b2 blank) the if statement wants to unfilter the spreadsheet and
ignores the criteria in cells c2 and d2. The information in these cells is
based on a drop down list could this have anything to do with my issue's?

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
 
Patrick,

My Bad I had a typo, your suggestion worked perfectly. Thanks again for your
help.
 
Back
Top