• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Advanced Filter Using Data Validation List - Macro

S

sshea

New Member
I have a sheet of vendors and their contact info. I would like people in my department who have access to this sheet to be able to use a dropdown list to View All Vendors or View Vendors by Field. So far I have successfully used the different vendor field selections in a data validation list combined with the macro below to filter the information by vendor field. However, the people accessing this are not computer savvy and to return to viewing all the vendors, disregarding their fields, you have to click on the cell containing the list and click keyboard key "Delete" to cancel the macro. I am new to Excel and am assuming that in the macro "If Target.Count > 1 Then Exit Sub" means if the cell is blank then cancel the filter.


I would like a way to have an option/selection in my data validation list of vendor fields say "View All" or just "ALL" which when selected would cancel the macro or somehow with some formula show all the vendors regardless of their field.


Option Explicit

' Developed by Contextures Inc. <--http://www.contextures.com/excelfiles.html#Filter

' www.contextures.com <-http://www.contextures.com/AdvFilterSearchWord.zip

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Target.Address = "$D$2" Then

Range("DatabaseUse").AdvancedFilter _

Action:=xlFilterInPlace, _

CriteriaRange:=Range("Criteria"), Unique:=False

End If

End Sub
 
Hi ,


The Excel help on the AdvancedFilter method has this :

[pre]
Code: 
Parameters

Action Required XlFilterAction One of the constants of XlFilterAction specifying
whether to make a copy or filter the list in place. 

CriteriaRange Optional Variant The criteria range. If this argument is omitted,
there are no criteria. 

CopyToRange Optional Variant The destination range for the copied rows
if Action is xlFilterCopy. Otherwise, this argument is ignored. 

Unique Optional Variant True to filter unique records only. False to filter
all records that meet the criteria. The default value is False.
If the CriteriaRange parameter is omitted , there are no criteria ; so , if you select ALL from the validation list , just check for the ALL selection , and use a different AdvancedFilter statement :

Option Explicit

' Developed by Contextures Inc. <--http://www.contextures.com/excelfiles.html#Filter
' http://www.contextures.com <-http://www.contextures.com/AdvFilterSearchWord.zip

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

If Target.Address = "$D$2" Then
If Target = "ALL" Then
Range("DatabaseUse").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:="", Unique:=False
Else
Range("DatabaseUse").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria"), Unique:=False
End If
End If
End Sub
[/pre]
Narayan
 
Thank you so much!! I see how you changed it and it worked! I just need to work more with these to understand the lingo... Your help is much appreciated!
 
Ok, when I click through the drop down list options it works for every option including if I click ALL. But once I click ALL and then try to click any other options again to sort it an error comes up "Run-time error '1004': Mathod 'Range' of object_Worksheet' failed" and then this part is highlighted:


Range("DatabaseUse").AdvancedFilter _

Action:=xlFilterInPlace, _

CriteriaRange:=Range("Criteria"), Unique:=False
 
Hi ,


How are you going about the task of selecting the ALL option ?


Is your Data Validation list inclusive of the ALL option , or are you replacing the original list with the ALL entry ?


Can you specify the range you are using for the Data Validation list , and what the entries are in this list ?


When you get the error message , can you enter the following command in the Immediate Window , and see what you get :


?Range("Criteria").address


Narayan
 
Thanks for your quick response.


?Range("Criteria").address


"Run-time error '1004':

Application-defined or object defined error"


_______________________________________


The data validation list is "ALL, Plumbing, Electrical, Excavation, Carpentry, Masonry, Other"


A4 is "Feild" and A5-A102 have whatever that row's vendor's feild is (one of: Plumbing, Electrical, Excavation, Carpentry, Masory, or Other).


W2 is =OR(ISNUMBER(SEARCH($D$2,A5)))


Advanced Filter:

List Range: $A$4:$U$102

Criteria Range: $W$1:$W$2


Everything was working with these formulas originally (the data validation list did not include ALL originally) accept I wanted to add the option of ALL to the list so that people didn't have to clear the cell to view everything.


What you suggested above works until I click from a specific field to "ALL" then to anything else. When I open the spreadsheet the lists starts on "ALL" so if I'm using the list for example ALL -> Plumbing -> Excavation -> ALL -> Electical It keeps filtering the list successfully until I try to go from ALL -> Electrical. That's when it gives me the error which I noticed also changes the Advanced filter Criteria Range. It seems to just delete that criteria range.


I have also tried puting the word ALL in V5:V102 and working out a code that way but was unsuccessful.


I hope I am explaining this situation clearly.
 
Hi ,


Sorry about the confusion ; I tried out your situation with some sample data , and let me put down the details :


1. I entered the following items in the range Q1:Q7.

[pre]
Code: 
ALL
Plumbing
Electrical
Excavation
Carpentry
Masonry
Other
I named this range [b]Data_Validation_List.


2.  I created a Data Validation drop-down in cell D2 , and selected List , and entered the formula =Data_Validation_List.


3.  In D1 I entered the same header that was in A4 ; in my case I labelled it Field ; A4 also had the text Field in it.


4.  The following procedure was inserted in the VBE :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

If Target.Address = "$D$2" Then
If Target = "ALL" Then
ActiveSheet.ShowAllData
Else
Range("DatabaseUse").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria"), Unique:=False
End If
End If
End Sub
[/pre]
5. I tried out various situations , selecting the options at random , and everything seems to work O.K.


In addition , I had created the following named ranges :


a. DatabaseUse referring to : =Sheet2!$A$4:$A$20


b. Criteria[/b] referring to : =Sheet2!$D$1:$D$2


You can change / extend these addresses as you wish.


I do not understand the reason for W2 and the formula , and how that has been defined as the Criteria.


Narayan
 
You must log in or register to reply here.
Back
Top