Excel VBA - check if string (cell value) exists in New Collection

[Harry Flashman]

How can I check if an item exist within a New Collection?

I have a routine which put unique cell values into a new collection if those cells values meet a particular criteria. This works well. I can output the collection values on another worksheet fine.

The object variable MyRng4 refers to a column of data
NewColl is the name for my New Colleciton

On Error Resume Next
    For Each MyCell In MyRng4.Cells
    If MyCell.Offset(0, -1).Value = strBrandLimit Then
        NewColl.Add MyCell.Value, CStr(MyCell.Value)
    End If
 Next MyCell

After I put these items into my collection, I now want to loop back through my range and delete all the rows where the cell value is NOT in the collection.

I realize I could probably pass my collection into an array and then check to see if the item is within that array - but I feel there must be away to check if a string is in a New Collection, and if there is way I would like to learn that method.

Any help would be greatly appreciated. Cheers.

 

[Harry Flashman]

Further to the information above...I had something like this in mind

For Each MyCell In MyRng4.Cells
          chk = NewColl.Item(MyCell)
        ' If item is not there then do something
Next MyCell

But I am not sure what to do. I presume I need some kind of error handler.

 

[AlphaFrog]

You could use the AutoFilter feature to filer for rows <> strBrandLimit then delete the filterd rows.

    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]With[/color] MyRng4.Offset(, -1)
        .AutoFilter 1, "<>" & strBrandLimit
        .Offset(1).Resize(.Count - 1).EntireRow.Delete
        .Parent.AutoFilterMode = [color=darkblue]False[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]

Alternatively, you could use a Dictionary object. It's a special collection with an .Exists method.

 

[Harry Flashman]

You could use the AutoFilter feature to filer for rows <> strBrandLimit then delete the filterd rows.

I am at home now and unfortunately I don't have a working version of excel on my home computer (which has decided it does not like Office 2013 anymore but that is another story).
I think the filter method might not work (although I will conduct experiments tomorrow).

I don't actually want to delete all rows that do not contain the value strBrandLimit (column A).
Here is an basic idea of how the data is structured:

Column A is a series of Questions
Column B is a series of Brands (and some other entities)
Column B is not a unique list. The brands are repeated for each question (but not all brands are repeated for each question).

The purpose of the New Collection is to extract a unique list of brands.
I want to delete all rows that contain brands that are not in that list (or at least I want the option to do something like that).

I could put the unique list in a named range on another worksheet, and perhaps use a formula to determine which brands are not in the list, and then delete those rows. But I thought there would be someway which involves whether the brands exist within the collection.

I will investigate the Dictionary object. That .Exist property method sounds like it might be what I need. Thanks.

If anyone else has any suggestion regarding checking to see if an item exists with a collection I would like to hear. My question is not just about solving this specific problem, but learning how to work with Collections in general.

 

[Harry Flashman]

I am still open to suggestions about how to solve my problem, which I think I have not explained very well.
Here is a simplified example of the data I am working with:

[TABLE="width: 176"]
<tbody>[TR]
[TD]Criteria
[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Criteria 1[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]Criteria 1[/TD]
[TD]Pat
[/TD]
[/TR]
[TR]
[TD]Criteria 1[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]Criteria 1[/TD]
[TD]Andy[/TD]
[/TR]
[TR]
[TD]Criteria 2[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Criteria 2[/TD]
[TD]Zack[/TD]
[/TR]
[TR]
[TD]Criteria 2[/TD]
[TD]Zack[/TD]
[/TR]
[TR]
[TD]Criteria 2[/TD]
[TD]Kyle[/TD]
[/TR]
[TR]
[TD]Criteria 2[/TD]
[TD]Andy[/TD]
[/TR]
[TR]
[TD]Criteria 2[/TD]
[TD]Andy[/TD]
[/TR]
[TR]
[TD]Criteria 3[/TD]
[TD]Pat[/TD]
[/TR]
[TR]
[TD]Criteria 3[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Criteria 3[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]Criteria 3[/TD]
[TD]Pat[/TD]
[/TR]
[TR]
[TD]Criteria 4[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Criteria 4[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Criteria 4[/TD]
[TD]Kyle[/TD]
[/TR]
[TR]
[TD]Criteria 4[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]Criteria 4[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Criteria 5[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Criteria 5[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]Criteria 5[/TD]
[TD]Kyle[/TD]
[/TR]
</tbody>[/TABLE]

Neither column A or B contains unique values
I want to extract a unique list of names from column B that meet the criteria in column A
I then want to loop through each cell in column B and IF the name is in my unique list that I have just created do something to that row.

This is the code I have come up with:

Sub CheckIfStringIsInCollection()

Dim ws As Worksheet
Dim MyRng As Range
Dim MyRng2 As Range ' My range minus the header
Dim MyRngC As Range ' a single column within MyRng
Dim MyCell As Range
Dim MyArray()
Dim i As Long

Dim UniqueColl As New Collection
Dim UniqueVal As Variant

Set ws = ActiveSheet
Set MyRng = ws.Range("A1").CurrentRegion
Set MyRng2 = MyRng.Resize(MyRng.Rows.Count - 1).Offset(1, 0)
Set MyRngC = MyRng2.Columns(2)

On Error Resume Next
For Each MyCell In MyRngC.Cells
    If MyCell.Offset(0, -1).Value = "Criteria 2" Then
        UniqueColl.Add MyCell.Value, CStr(MyCell.Value)
    End If
Next MyCell
On Error GoTo 0

For Each UniqueVal In UniqueColl
        ReDim Preserve MyArray(i)
        MyArray(i) = UniqueVal
        i = i + 1
Next UniqueVal

For Each MyCell In MyRng2.Cells
    If IsInArray(MyCell.Value, MyArray) Then
        MyCell.EntireRow.Font.Bold = True
    Else
        MyCell.EntireRow.Font.Bold = False
  End If
Next MyCell

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

The code uses New Collection to extract a unique list
Each item is then passed to an array.
I then use a Function IsInArray to check which cells contain a name that is in the array.
For cells where the name is in the array the entire row is then bolded.

It works pretty well. So I guess I have solved my own problem. But if there is a better method I would love to hear about it.
I am still yet to figure out how to use the Dictionary Object - so if someone can give me some tips there I am all ears. Cheers.