+ Reply to Thread
Results 1 to 4 of 4

VBA - Exclude value of a cell

  1. #1
    Registered User
    Join Date
    11-29-2018
    Location
    Derbyshire, England
    MS-Off Ver
    Office 98, Office 2013
    Posts
    2

    VBA - Exclude value of a cell

    Hello all, just trying to sort out a macro contained on an Excel sheet which filters a table range.

    The table (containing names and several other details) is presented to the user in raw, unfiltered format. The user selects from between 1-6 data validation boxes and - when running the macro - VBA will take the values in the boxes and apply a filter to include the desired values. This works successfully using the below code and means the table is filtered to what they want to see:

    Worksheets("Consultant List").Select
    ActiveSheet.Range("A9:H300").AutoFilter Field:=1, Operator:=xlFilterValues, _
    Criteria1:=Split(WorksheetFunction.TextJoin(",", True, Range("E1:E6")), ",")


    The only other filter required is the one I cannot figure out how to code! It needs to run on the same range at the same time. Quite simply, it's a second filter (on a separate column) which the user can apply by choosing from a data validation cell. The idea is that they would select a value and when the macro is ran, VBA should exclude the value specified. The code I've tried (and failed with) is:

    ActiveSheet.Range("A9:H300").AutoFilter Field:=5, Criteria1:=("<>G5")

    I've tried recording the macro, but this results in every other autofilter being listed which doesn't work. I've made sure the data is formatted correctly and that the values in the data validation list are taken from an extract of a column in the table.

    Hoping somebody can help!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: VBA - Exclude value of a cell

    Untested, but maybe:
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-29-2018
    Location
    Derbyshire, England
    MS-Off Ver
    Office 98, Office 2013
    Posts
    2

    Re: VBA - Exclude value of a cell

    Quote Originally Posted by TMS View Post
    Untested, but maybe:
    Please Login or Register  to view this content.

    Wow, so simple but so effective -- works perfect thank you (added to your rep.)

    I was going wrong by combining <> and the cell reference.

    Thanks again!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: VBA - Exclude value of a cell

    You're welcome. Thanks for the rep.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. cannot exclude a cell when using AVERAGEIF
    By surfing96 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-27-2021, 06:18 AM
  2. How to select cell but exclude first 2 row?
    By jp16 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-14-2018, 09:50 PM
  3. Add EXCLUDE Option in table to Exclude from Solver
    By lbofbb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-17-2015, 09:05 AM
  4. Copy cell value to new cell and exclude all spaces in process
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-28-2011, 03:42 PM
  5. How to exclude a cell from array
    By ezhenya in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-02-2010, 04:27 AM
  6. [SOLVED] Exclude a cell
    By DF2507 in forum Excel General
    Replies: 8
    Last Post: 07-10-2006, 03:29 AM
  7. Exclude a cell again
    By DF2507 in forum Excel General
    Replies: 2
    Last Post: 07-10-2006, 03:29 AM

Tags for this Thread

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