• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter with multiple values contains

Hello,

I am trying to create a code wherein the N column needs to be filtered with the cell values starting with "O", "S", "L", "M", "N". I tried with ARRAY but it does not work if the items are more than 2. Can you help me here to find the correct code?

Regards,
Niranjan
 

vletm

Excel Ninja
Niranjanrajrishi
Your original dream was: ... needs to be filtered with the cell values starting with "O", "S", "L", "M", "N" ...
... and now, Your writings gives image of something different ( eg "OO*", "?O*", "*O" ),
there seems to be unlimited versions or how?
That could soon mean like "*"!

There are possibilities,
but You should know,
what do You would like to do?
and
How? with formulas (as You've written this) or other way.
 

vletm

Excel Ninja
Niranjanrajrishi
I did two questions.
... and without answers, maybe someone else would start to guess.

... or as You should want to have a formula solution then ...
1) write eg to cell AAA1 text OSLMN
2) copy&paste below formula eg to cell AAA2
=IF(N2<>"",IFERROR(FIND(LEFT(N2,1),AAA$1,1),"no"),"-")
3) Copy down as needed
4) use normal filter functionality to get Your needed result
 
Last edited:

Fluff13

Active Member
How about
Code:
Sub Niranjanrajrishi()
   Dim Cl As Range
  
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
         Select Case Left(Cl, 1)
            Case "O", "S", "L", "M", "N"
               .Item(Cl.Value) = Empty
         End Select
      Next Cl
      Range("N:N").AutoFilter 1, .Keys, xlFilterValues
   End With
End Sub
 
Hey,

How about
Code:
Sub Niranjanrajrishi()
   Dim Cl As Range
 
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
         Select Case Left(Cl, 1)
            Case "O", "S", "L", "M", "N"
               .Item(Cl.Value) = Empty
         End Select
      Next Cl
      Range("N:N").AutoFilter 1, .Keys, xlFilterValues
   End With
End Sub
Hey,

This code works perfectly when the data exists. After filter, if there is no data then it shows error at "Range("N:N").AutoFilter 1, .Keys, xlFilterValues".
 

Fluff13

Active Member
In that case, how about
Code:
Sub Niranjanrajrishi()
   Dim cl As Range
  
   With CreateObject("scripting.dictionary")
      For Each cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
         Select Case Left(cl, 1)
            Case "O", "S", "L", "M", "N"
               .Item(cl.Value) = Empty
         End Select
      Next cl
      If .Count > 0 Then Range("N:N").AutoFilter 1, .Keys, xlFilterValues
   End With
End Sub
 
In that case, how about
Code:
Sub Niranjanrajrishi()
   Dim cl As Range
 
   With CreateObject("scripting.dictionary")
      For Each cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
         Select Case Left(cl, 1)
            Case "O", "S", "L", "M", "N"
               .Item(cl.Value) = Empty
         End Select
      Next cl
      If .Count > 0 Then Range("N:N").AutoFilter 1, .Keys, xlFilterValues
   End With
End Sub
Thank you. It worked!
 
Top