Announcement

Collapse
No announcement yet.

How to Automatically Autofilter Based Upon 'Contains Cell Value'

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to Automatically Autofilter Based Upon 'Contains Cell Value'



    Hi all -

    Im very familiar with how to write a macro to automatically autofilter a list based upon a Cell Value (i.e. Range =(A1)...etc.).... but what I cannot figure out how to correctly write is a way to have Excel automatically autofilter a list for any row that CONTAINS the Cell Value, instead of just the exact value.

    Cell Value = "birds"
    Example -- I need to filter every row so that I see every row that CONTAINS "birds" in the character-string, not just the row that = "birds".

    a) is this possible?

    If not, I'd like to know also so I can stop attempting to guess (-;
    Thanks!!!

    Matt

  • #2
    Re: How to Automatically Autofilter Based Upon 'Contains Cell Value'

    Did you event record a macro with autofilter prepared with selection as Contains = birds
    Triumph without peril brings no glory: Just try

    Comment


    • #3
      Re: How to Automatically Autofilter Based Upon 'Contains Cell Value'

      Autofilter is using Criteria, which can use wildcards (*?). So just set your Criteria string to "*birds*"

      Comment


      • #4
        Re: How to Automatically Autofilter Based Upon 'Contains Cell Value'

        Hey guys -

        Thanks for that advice -- it is a simple fix if the criteria I needed was just plain text, such as "birds", however, what I need it to do is be able to reference the text found in a specific cell. I cannot for the life of me figure out how to correctly get the Criteria1:=Range("A2").Text to work inside of " " marks & include the * * marks needed to make it look for anything containing the cell reference, not just anything that equals the cell.

        Is my question clear, or not very clear? I'm sorry for the convoluted description

        Comment


        • #5
          Re: How to Automatically Autofilter Based Upon 'Contains Cell Value'

          Post the line of VBA code where you are setting the Criteria. Someone will figure it out.

          Comment


          • #6
            Re: How to Automatically Autofilter Based Upon 'Contains Cell Value'

            Dimension the criteria as a string variable like so:

            Code:
            Dim sCriteria as String
            sCriteria = "*" & Range("A2").value & "*"
            Then pass this variable to the filter:
            Code:
            Criteria1:=sCriteria
            AAE
            ----------------------------------------------------

            Forum Rules | Message to Cross Posters | How to use Tags

            Comment


            • #7


              Re: How to Automatically Autofilter Based Upon 'Contains Cell Value'

              That worked perfectly. Thank you all so much!!

              Comment

              Working...
              X