Advanced Filter - Criteria Range Issue

Hi,

Welcome to the forum. Please take a moment or two to read the forum rules. In this case particularly the one which asks you to wrap VBA code and XL functions in Code tags.

If you change it I'm sure someone will be along to help.

Regards,

Richard Buttrey - Moderator
_____________


Hi. I created a worksheet with a data table (80 columns x 212 rows) and a separate tab (I'll call it Tab SEARCH) with a set of drop down menus. The goal is to allow anyone who uses the file to create their own filter criteria using the drop down menus. if more than one drop down menu is selected, criteria from every drop down menue must be true.

I placed my criteria range on TAB SEARCH, Cells A1:F2. Button to Run Search is also included on TAB SEARCH.

When I try to run my filter using all the cells in the criteria range, the filter results are not as expected. When I modify my criteria range to cells A1:C2, the filter results are as expected (all rows that fit criteria show up). Using a range with more than 3 criteria yields whacky, incomplete results, or no results at all. Is there a limit to the # of filter fields I can include in the advanced criteria criteria range? is there a work-around.

Is there something I can add to the VBA Code that will make this work?
Sub Button6_Click()
Sheets("SEARCH").Range("G7").Value = 0
Sheets("SEARCH").Range("G8").Value = 0
Sheets("SEARCH").Range("G12").Value = 0
Sheets("SEARCH").Range("G17").Value = 0
Sheets("SEARCH").Range("G20").Value = False
Sheets("SEARCH").Range("G28").Value = 0
Sheets("SEARCH").Range("G33").Value = 0
Sheets("SEARCH").Range("G38").Value = 0
End Sub
Sub Button7_Click()
Dim numberx As Integer
Dim numbery As Integer
Dim numberz As Integer
numberx = Sheets("SEARCH").Range("G28").Value
numbery = Sheets("SEARCH").Range("G33").Value
numberz = Sheets("SEARCH").Range("G38").Value


Sheets("ALL").Select

If numberx = 0 And numbery = 0 And numberz = 0 Then
Range("A1:CD212").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("SEARCH").Range("A1:f2"), CopyToRange:=Range("A230:N230"), Unique _
:=False

Else: Range("A1:CD212").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("SEARCH").Range("A1:F2"), CopyToRange:=Range("A230:N230") _
, Unique:=False
End If

Rows("230:465").Select
Selection.Copy
Sheets("RESULTS").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub