CODEWITHSUNDEEP
vbaexcel
pexmar
pexmar

Reputation: 341

Handle visible cells in vba

I have a Worksheet named 'Abschluss'. In this worksheet I use filter to get the data-range I want to handle with my vba script. So i only want to handle the visible rows. 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)
Next

This doesn't work, I get runtime-error 438. Do you know where the problem is? I assume that Worksheets("Abschluss").SpecialCells(xlCellTypeVisible).Rows returns the wrong data-type, but I couldn't fix it.

Upvotes: 3

Views: 559

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

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

Try this

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

Upvotes: 2

Jeanno
Jeanno

Reputation: 2859

Change to 

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

Upvotes: 0

Related Questions