Conditional Testing of 3 cells

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
 
P

Patrick Molloy

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
 
T

tpeter

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
 
T

tpeter

Patrick,

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top