+ Reply to Thread
Results 1 to 1 of 1

Advanced Filter - Criteria Range Issue

  1. #1
    Registered User
    Join Date

    Advanced Filter - Criteria Range Issue


    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.


    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


    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 _

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

    Application.CutCopyMode = False

    End Sub
    Attached Files Attached Files
    Last edited by Richard Buttrey; 06-27-2008 at 05:47 PM. Reason: Code tags missing from code section of post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1