Identify cells with unknown variable

[zombiemaster]

I have a spreadsheet with 8 columns of data. In the 3rd column, I have a variety of codes that start with 'B', 'D' and O'.

However, those are just the ones that we KNOW OF in today's world. Management isn't sure if there could be other potential codes or not, and want me to include VBA that will split any 'new' codes that come along in the future out into it's own tab.

So to start with, I wanted to insert a filter in that column that finds anything OTHER than those three known codes. I thought this would work:

    ActiveSheet.Range("$D$3:$D" + endRow$).AutoFilter Field:=1, Criteria1:="<>D*", _
        Operator:=xlAnd, Criteria2:="<>B*", Operator:=xlAnd, Criteria3:="<>O*"

But it doesn't work - I'm getting a runtime error.

I know Excel can't do more than two qualifiers if doing a filter manually, but I thought I could add "Criteria3" within the code and it would work - was that an incorrect assumption? If I take out that third criteria, it works and all I see are the "O" entries.

    ActiveSheet.Range("$D$3:$D" + endRow$).AutoFilter Field:=1, Criteria1:="<>D*", _
        Operator:=xlAnd, Criteria2:="<>B*"

Once I get this piece nailed down on how to identify if there are unknown codes, I will need a way to split those out to another sheet...but first things, first!

Thanks for any help,
~ZM~

 

[Fluff]

You can only filter on more than two values if they are an exact match.
Have a look at advanced filters instead.
https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/

 

[Fluff]

Alternatively, try

Sub zombiemaster2()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("D4", Range("D" & Rows.count).End(xlUp))
         Select Case Left(Cl, 1)
            Case "D", "B", "O"
            Case Else
            .Item(Cl.Value) = Empty
         End Select
      Next Cl
      If .count > 0 Then Range("D3", Range("D" & Rows.count).End(xlUp)).AutoFilter 1, .keys, xlFilterValues
   End With
End Sub

 

[zombiemaster]

Thank you yet again, Fluff - I don't understand all of what you have here, but it does work and is amazing! I tried changing one of my data items from "B" to "M" and ran your code and it filtered perfectly - that one item was the only one that was left - pure MAGIC!

Now, if you have time and feel like continuing to help this poor old brain, now that I have a filtered list that excludes B, D and O, I want to copy any discovered items out to a new tab called "RESEARCH", then delete those items from the original tab. I am thinking it would be another IF THEN statement but the code is beyond me. I would also need it to ignore that section entirely if .count = 0.

Thanks again,
~ZM~

 

[Fluff]

Assuming the data is in cols B:I try

Sub zombiemaster2()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = ActiveSheet
   If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws.Range("D4", Ws.Range("D" & Rows.Count).End(xlUp))
         Select Case Left(Cl, 1)
            Case "D", "B", "O"
            Case Else
            .Item(Cl.Value) = Empty
         End Select
      Next Cl
      If .Count > 0 Then
         Ws.Range("B3:I3").AutoFilter 3, .Keys, xlFilterValues
         Ws.AutoFilter.Range.EntireRow.Copy Sheets("RESEARCH").Range("A1")
         Ws.AutoFilter.Range.Offset(1).EntireRow.Delete
         Ws.AutoFilterMode = False
      End If
   End With
End Sub

 

[zombiemaster]

Once again I am in your debt, Fluff - after some small adjustments (I had a duplicate Dim Ws As Worksheet in another part of the code), this is working like a charm!

Now, I just need to figure out how to clone you and hide you here in the office all the time to pick your brain so I look good to management ...

Thanks!
~ZM~

 

[Fluff]

You're welcome & thanks for the feedback