Remove item from collection based on a condition

user_19080542 0
Viewed 41

How do I remove an item from a collection, if that items (numerical item) is greater than a number, say 100.

My intention below:

dim XY as New Collection
 For Each Item In XY
                If XY.Item > Cells(11, 2) + Cells(12, 2) 
                                   'these two cells = 100 
                 Then XY.remove

Or is it not possible to remove things within the collection based on this criteria, and I'd have to paste it in excel and delete it from there?

vba excel
1 Answers

Remove Greater-Than-Items From a Collection

The Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Removes all numeric items, greater than a value ('GTValue'),
'               from a collection ('coll').
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub CollRemoveGT( _
        ByRef Coll As Collection, _
        ByVal GTValue As Double)
    
    Dim Item As Variant
    Dim n As Long
    
    For n = Coll.Count To 1 Step -1
        Item = Coll(n)
        If IsNumeric(Item) Then
            If Item > GTValue Then
                Coll.Remove n
            End If
        End If
    Next n
    
End Sub

Utilization (Example)

Sub CollRemoveGTTEST()
    
    ' Write some example values to an array ('CollValues').
    Dim CollValues() As Variant: CollValues = Array(101, "ABC", 99, 2, 100, 150)
    
    ' Reference the worksheet ('ws').
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    ' Calculate the criteria (greater than) value ('GTValue').
    Dim GTValue As Variant: GTValue = Application.Sum(ws.Range("B11:B12"))
    
    ' A reminder that 'Sum' fails if there are error values in the range.
    If IsError(GTValue) Then
        MsgBox "Error values found in sum range.", vbCritical
        Exit Sub
    End If
    
    Dim Coll As Collection
    Dim Item As Variant
    
    ' Reference a new collection ...
    Set Coll = New Collection
    '... and populate it with the values from the array.
    For Each Item In CollValues: Coll.Add Item: Next Item
    
    ' Print before.
    Debug.Print "Before (" & Coll.Count & " Items)"
    For Each Item In Coll: Debug.Print Item: Next Item
    
    ' Remove the values greater than the criteria value from the collection.
    CollRemoveGT Coll, GTValue
    
    ' Print after.
    Debug.Print "After (" & Coll.Count & " Items)"
    For Each Item In Coll: Debug.Print Item: Next Item

End Sub

Results (SUM(B11:B12) = 100) in the Immediate Window (Ctrl+G)

Before (6 Items)
 101 
ABC
 99 
 2 
 100 
 150 
After (4 Items)
ABC
 99 
 2 
 100
user_98140690
Related