Handle visible cells in vba

I have a worksheet named "Abschluss". In this sheet, I am using a filter to get the data range that I want to process with my vba script. So I want to handle visible lines. My vba Script looks like

For Each i In Worksheets("Abschluss").SpecialCells(xlCellTypeVisible).Rows
    If (WorksheetFunction.CountIf(Range("B2:B" & i), Cells(i, 2)) = 1) Then _
        Umsetzung_Kapitel_1.AddItem Cells(i, 2)


It doesn't work, I get runtime error 438. Do you know where the problem is? I am assuming it is Worksheets("Abschluss").SpecialCells(xlCellTypeVisible).Rows

returning the wrong data type, but I have not been able to fix it.


source to share

2 answers

It looks like you are trying to populate a combobox or list with unique items, perhaps in a UserForm?

try it

Private Sub UserForm_Initialize()
    Dim cUnique As Collection
    Dim Rng As Range
    Dim Cell As Range
    Dim sh As Worksheet
    Dim vNum As Variant

    Set sh = ThisWorkbook.Sheets("Abschluss")
    Rws = sh.Cells(Rows.Count, "B").End(xlUp).Row
    Set Rng = sh.Range(sh.Cells(2, 2), sh.Cells(Rws, 2)).SpecialCells(xlCellTypeVisible)

    Set cUnique = New Collection

    On Error Resume Next
    For Each Cell In Rng.Cells
        cUnique.Add Cell.Value, CStr(Cell.Value)
    Next Cell
    On Error GoTo 0

    For Each vNum In cUnique
        ComboBox1.AddItem vNum
    Next vNum

End Sub




Change to

Dim i As Long
Dim colCount As Long: colCount = Worksheets("Abschluss").SpecialCells(xlCellTypeVisible).Rows.Count
For i = 1 to colCount




All Articles