VBA - Error 381 while trying delete items from combobox

Question

I've tried by hours to cope with this error, search solutions, but nothing is clear for me.

I've imported successfully items from Excel to combobox (>19 appearances)
Now I have duplicates in combo. I wanna iterate by Excel sheet, compare with Combobox & remove unnecessary items (except single one)
I have

error 381 - Could not get the Column property array index.

Dim N As Long, K As Long, counter As Long
With Sheets("Główne")
    N = .Cells(Rows.Count, 12).End(xlUp).Row
End With

Dim ostatnia As Long

ostatnia = Cells(Rows.Count, 11).End(xlUp).Row

For i = 1 To ostatnia
    Range("I" & i + 1).Formula = "=COUNTIFS(L:L,L" & i + 1 & ")"
Next

ComboBox1.Clear
For K = 1 To N
    If Cells(K + 1, 9).Value > 19 Then
        ComboBox1.AddItem Sheets("Główne").Cells(K + 1, 12).Value
    End If
Next K

Range("I2:I" & ostatnia).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'############### problem is somewhere below ##############'
For S = 2 To N
    counter = 1
    For iteracjalista = 0 To ComboBox1.ListCount - 1

        If ComboBox1.Column(0, iteracjalista) = Sheets("Główne").Cells(S + 1, 12).Value Then

             If Sheets("Główne").Cells(S + 1, 9).Value > counter Then
                ComboBox1.RemoveItem 1
                counter = counter + 1
             End If

        End If

    Next iteracjalista
Next S

Probably problem is in last part of code. But I have no idea how I should fix it. Could you help me?

see this https://stackoverflow.com/questions/14392122/how-do-i-populate-a-combo-box-from-a-column-in-my-excel-spread-sheet — Tin Bum, Nov 11 '19 at 00:23

score 0 · Answer 1 · answered Nov 11 '19 at 00:59

In place of this Code

For K = 1 To N
    If Cells(K + 1, 9).Value > 19 Then
        ComboBox1.AddItem Sheets("Główne").Cells(K + 1, 12).Value
    End If
Next K

Use this Code - It eliminates Duplicates before populating the ComboBox

   Dim xList As String, xVal As String

   ' The following populates the ComboBox with Unique Values - No Duplicates
     xList = ""
     ' We are using the colon character ":" as a separator
     ' You may wish to use something else
     For K = 1 To N
        xVal = Cells(K + 1, 9).Value
        If xVal > 19 Then
           If InStr(1, xList, ":" & xVal, vbTextCompare) = 0 Then
              xList = xList & ":" & xVal
           End If
        End If
     Next K
     xList = Mid(xList, 2)  ' Remove the leading : character
     ThisWorkbook.Sheets("Glówne").ComboBox1.List = Split(xList, ":")
   ' Done

Then you can takeout all the existing code for deleting duplicates from the ComboBox .... All the following can be deleted

'############### problem is somewhere below ##############'
For S = 2 To N
    counter = 1
    For iteracjalista = 0 To ComboBox1.ListCount - 1

        If ComboBox1.Column(0, iteracjalista) = Sheets("Główne").Cells(S + 1, 12).Value Then

             If Sheets("Główne").Cells(S + 1, 9).Value > counter Then
                ComboBox1.RemoveItem 1
                counter = counter + 1
             End If

        End If

    Next iteracjalista
Next S

VBA - Error 381 while trying delete items from combobox

1 Answers1