loop through listbox with condition

[abdelfattah]

hello
i need simple code when i show listbox in userform i would only data contain expired
i have sheet1 contains data 6 columns begin from a2:f200 the column 6 (f) contains expired when i run userform it show data only expired in listbox

 

[Fluff]

How about

Private Sub UserForm_Initialize()
    Dim Ary As Variant, Nary As Variant
    Dim r As Long, c As Long, nr As Long
    
    With Sheets("sheet1")
        nr = Application.CountIf(.Range("F:F"), "Expired")
        If nr = 0 Then Exit Sub
        Ary = .Range("A1").CurrentRegion.Value2
    End With
    ReDim Nary(1 To nr, 1 To UBound(Ary, 2))
    nr = 0
    For r = 2 To UBound(Ary)
        If LCase(Ary(r, 6)) = "expired" Then
            nr = nr + 1
            For c = 1 To UBound(Ary, 2)
                Nary(nr, c) = Ary(r, c)
            Next c
        End If
    Next r
    Me.ListBox1.List = Nary
End Sub

 

[abdelfattah]

Dim Ary As Variant, Nary As Variant Dim r As Long, c As Long, nr As Long With Sheets("sheet1") nr = Application.CountIf(.Range("F:F"), "Expired") If nr = 0 Then Exit Sub Ary = .Range("A1").CurrentRegion.Value2 End With ReDim Nary(1 To nr, 1 To UBound(Ary, 2)) nr = 0 For r = 2 To UBound(Ary) If LCase(Ary(r, 6)) = "expired" Then nr = nr + 1 For c = 1 To UBound(Ary, 2) Nary(nr, c) = Ary(r, c) Next c End If Next r Me.ListBox1.List = Nary

it gives me error runtime error 70 premission denied

 

[Fluff]

What line gave the error?

 

[abdelfattah]

sorry for give me the rowsource causes the error now the code is great thanks so much

 

[Fluff]

You're welcome & thanks for the feedback