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

Harry Flashman

Active Member
Joined
May 1, 2011
Messages
361
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

Code:
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.
 
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Further to the information above...I had something like this in mind

Code:
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.
 
Upvote 0
You could use the AutoFilter feature to filer for rows <> strBrandLimit then delete the filterd rows.

Code:
    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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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:

Criteria
Name
Criteria 1Mary
Criteria 1Pat
Criteria 1Sam
Criteria 1Andy
Criteria 2Bob
Criteria 2Zack
Criteria 2Zack
Criteria 2Kyle
Criteria 2Andy
Criteria 2Andy
Criteria 3Pat
Criteria 3Bob
Criteria 3Jane
Criteria 3Pat
Criteria 4Bob
Criteria 4Bob
Criteria 4Kyle
Criteria 4Jane
Criteria 4Bob
Criteria 5Bob
Criteria 5Sam
Criteria 5Kyle

<tbody>
</tbody>

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:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top