+ Reply to Thread
Results 1 to 6 of 6

Help: Advance Filter

  1. #1
    Forum Contributor
    Join Date
    05-09-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    209

    Help: Advance Filter

    I want to make the file more automatic and user friendly. All the data set as table and it will auto run if there is any additional information.
    Refer to sheet [Search], maximum 5 criteria is allowed to search the data.
    Problem is all database will be shown if row 6 is not in use and left it blank. How to tell program blank mean not in use?

    Please refer to file attached.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Help: Advance Filter

    Hello
    If you leave the last row blank it will return All records. You could just not have the 5th. line until you need it, it is a dynamic table and the criteria will be updated to include the new line. Alternatively, with your Data Validation drop down lists select 'All' in one or more fields and the filter will ignore that line because there's no match in the Database. See the amended file I've attached. I'm using Macro2 on the 'Click Me' command button.

    Hope this helps.
    DBY
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    05-09-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    209

    Re: Help: Advance Filter

    Quote Originally Posted by DBY View Post
    Hello
    If you leave the last row blank it will return All records. You could just not have the 5th. line until you need it, it is a dynamic table and the criteria will be updated to include the new line. Alternatively, with your Data Validation drop down lists select 'All' in one or more fields and the filter will ignore that line because there's no match in the Database. See the amended file I've attached. I'm using Macro2 on the 'Click Me' command button.

    Hope this helps.
    DBY
    Refer to Macro 2, this code was removed. Without this code, sheets "show" will not deleted the previous record.
    Please Login or Register  to view this content.
    "All" = it can be one of the solution. It is possible make to write in macro that blank is to be ignore?

    Thanks.

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Help: Advance Filter

    Hello
    Try changing the Macro2 code to:

    Please Login or Register  to view this content.
    Refer to Macro 2, this code was removed. Without this code, sheets "show" will not deleted the previous record.
    Not sure why you would use this as whenever the Advanced filter is run it automatically removes the previous filter results.

    DBY

  5. #5
    Forum Contributor
    Join Date
    05-09-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    209

    Re: Help: Advance Filter

    Quote Originally Posted by DBY View Post
    Hello
    Try changing the Macro2 code to:

    Please Login or Register  to view this content.


    Not sure why you would use this as whenever the Advanced filter is run it automatically removes the previous filter results.

    DBY
    Thanks. It was work.

    the code I use to remove as below:
    Please Login or Register  to view this content.
    How is your code work to auto remove previous filter results? Which code is refer to this?
    Last edited by Shermaine2010; 07-04-2016 at 10:51 AM.

  6. #6
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Help: Advance Filter

    Glad all's working.

    How is your code work to auto remove previous filter results? Which code is refer to this?
    I didn't add any code for this. It is part of an in built feature of the Advanced Filter. The 'CopyToRange' is always cleared when run.

    DBY

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Cant filter 2 columns even with advance filter
    By pingpong1 in forum Excel General
    Replies: 19
    Last Post: 07-03-2015, 04:40 PM
  2. Advance Filter - vba
    By geliedee in forum Excel General
    Replies: 21
    Last Post: 02-20-2015, 05:06 AM
  3. [SOLVED] Advance Filter help
    By buwa84 in forum Excel General
    Replies: 5
    Last Post: 07-16-2013, 04:46 PM
  4. Advance Filter
    By betmck in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2007, 02:48 PM
  5. Advance filter
    By Obi-Wan Kenobi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-21-2006, 11:53 AM
  6. Advance filter search does not filter an exact match
    By cfiiland in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-10-2005, 08:05 AM
  7. [SOLVED] advance filter
    By DANmcc in forum Excel General
    Replies: 2
    Last Post: 04-20-2005, 06:06 PM

Bookmarks

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