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)
Next
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.
+3
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
+2
source to share