excelvbaloopslistbox

Separate listbox items to do a for each loop


I'm having trouble seperating my listbox items, so I can run them in a loop. I know this atm takes all items and tries to run them through, however I don't know how to seperate them. The code is following:

Dim SelectedItems As String
Dim LastRow As Long

LastRow = ActiveSheet.Range("F1").SpecialCells(xlCellTypeLastCell).Row

For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) = True Then
SelectedItems = SelectedItems & ListBox2.List(i) & vbNewLine
End If
Next i

If SelectedItems = "" Then
MsgBox "Please select minimum one country"
Else

For Each SelectedItems In ListBox2

    For i = 11 To LastRow

    If Range("F" & i).Value = SelectedItems Then
    Rows(i).EntireRow.Hidden = True
    Else: Rows(i).EntireRow.Hidden = False
    End If

    Next i
Next SelectedItems

can someone assist?


Solution

  • Dim SelectedItems As String, LastRow As Long
    Dim selItem As Variant, selItems As Variant
    
    LastRow = ActiveSheet.Range("F1").SpecialCells(xlCellTypeLastCell).Row
    
    For i = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(i) = True Then
            SelectedItems = SelectedItems & ListBox2.List(i) & vbNewLine
        End If
    Next i
    
    Stop
    If SelectedItems = "" Then
        MsgBox "Please select minimum one country"
    Else
        SelectedItems = left(SelectedItems, Len(SelectedItems) - 1)
        selItems = Split(SelectedItems, vbNewLine)
        For Each selItem In selItems
            For i = LastRow To 11 Step -1
                If CStr(Range("F" & i).value) = CStr(selItem) Then
                    Stop
                    Rows(i).EntireRow.Hidden = True
                    'Else: Rows(i).EntireRow.Hidden = False
                End If
            Next i
        Next
    End If
    

    Your code could not identify each selected Item from the string without splitting it in its elements. Edited: Transformed the code in a test one. I will explain you (in a comment) how to check.

    Note: I just tried to make your code workable. Otherwise, you can make the filtering directly, using Excel AutoFilter (in VBA, of course)...