CODEWITHSUNDEEP
vbaexcel
NuWin
NuWin

Reputation: 276

Excel VBA: Remove ListBox Item if in Array

Dim libRng As Range, item As Long
Dim refArrCheck As Variant, refArr As Variant

Set libRng = Sheets("Sheet2").Range("B2:F1000")
refArr = Sheets("Sheet1").Range("A1:BA1")

For Each refArrCheck In refArr
    For Each cell In libRng.Cells
        For item = Me.ListBox1.ListCount - 1 To 0 Step -1
            If Me.ListBox1.Column(0, item) = cell.Value And cell.Value = refArrCheck Then
              Me.ListBox1.RemoveItem (item)
            End If
        Next
    Next
Next refArrCheck

Essentially I am trying to tell Excel to look through libRng and compare it with the array refArr if there is any matches, remove item from ListBox. refArr values are items listed in the ListBox. The following code seems to "work" but it does not work properly in that when I run it, it only removes 1 item that matches when there are more that match.

Is my 3rd For Loop or If statement wrong? Also would it matter if some cells in libRng are blank?

Upvotes: 2

Views: 1268

Answers (1)

user4039065
user4039065

Reputation:

There appears to be a lot of unnecessary looping and checking. There are native worksheet functions that can search whole ranges at once to determine existence of a value.

Dim libRng As Range, refArr As Range, itm As Long

Set libRng = Worksheets("Sheet2").Range("B2:F1000")
Set refArr = Worksheets("Sheet1").Range("A1:BA1")

For itm = Me.ListBox1.ListCount - 1 To 0 Step -1
    If CBool(Application.CountIf(libRng, Me.ListBox1.Column(0, itm))) And _
       CBool(Application.CountIf(refArr, Me.ListBox1.Column(0, itm))) Then
            Me.ListBox1.RemoveItem (itm)
    End If
Next

The WorksheetFunction object canot use an array with a COUNTIF function but it can with a MATCH function.

Upvotes: 2

Related Questions