Userform Listbox selections to delete rows from worksheet

[ohiojarhead]

Good Morning,

I have created a userform with a listbox (Multi-Select) and four command buttons. The listbox is populated on UserForm Initialize with Warehouse names "Bldg A", "Bldg B", etc. The buttons list the standard time reports are to be run "6am Report" "8am Report" "12pm Report" and "4pm Report". I would like for the user to select the Warehouse names that they want to run the report for and then click the button. The data is kept on a Worksheet "PartShortage" and Column F contains the same values as the listbox. I need the rows to be deleted where the value in column F does not match one of the selected values from the listbox for the 6am Report. For the 8am report, all rows need to be deleted where the value in column F does not match the items selected in the listbox and the date/time in column P is not greater than or equal to Todays Date and 6:00:00 am. I have not been able to find any other threads out there that address this situation.

 

[ohiojarhead]

I just found this thread. This is similar to what Im looking to do.
http://www.mrexcel.com/forum/excel-...ows-activesheet-based-listbox-selections.html

 

[ohiojarhead]

I managed to put this together.

Private Sub CommandButton1_Click()
Dim text As String
    Dim i As Integer
 For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
        Firstrow = ActiveSheet.UsedRange.Cells(1).Row + 5
        Lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
            For lRow = Lastrow To Firstrow Step -1
            If ActiveSheet.Range("F" & lRow).Value <> ListBox1.Selected(i) Then ActiveSheet.Range("A" & lRow).EntireRow.Delete
    Next i
    End If
End Sub

I receive a Compile error: Invalid Next Control variable reference. The 10th line 'i' gets highlighted. Am I on the right track?

 

[Fluff]

Hi
Your missing this

        For lRow = Lastrow To Firstrow Step -1
           If ActiveSheet.Range("F" & lRow).Value <> ListBox1.Selected(i) Then ActiveSheet.Range("A" & lRow).EntireRow.Delete
        [COLOR=#ff0000]Next lRow[/COLOR]
    Next i

HTH

 

[ohiojarhead]

Thank you! I changed it to this:

Private Sub CommandButton1_Click()
Dim text As String
    Dim i As Integer
 For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
        Firstrow = ActiveSheet.UsedRange.Cells(1).Row + 5
        Lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
            For lRow = Lastrow To Firstrow Step -1
            If ActiveSheet.Range("F" & lRow).Value <> ListBox1.Selected(i) Then ActiveSheet.Range("A" & lRow).EntireRow.Delete
            Next lRow
    End If
    Next i     
End Sub

This sill does not work the way I need it to. I think I need an array that contains the listbox values. Looping through the listbox values, I end up deleting rows that should not be because of the order in the listbox. Any idea on how to do that?

 

[ohiojarhead]

Sorry to keep updating this thread, but I think I am getting close. I now have this:

Private Sub CommandButton1_Click()
Dim x As Long
Dim varSelected() As String
Dim i As Integer
'Save Values of Listbox to an array
i = i - 1
With ListBox1
    For x = 0 To .ListCount - 1
        If .Selected(x) Then
            i = i + 1
            ReDim Preserve varSelected(i)
            varSelected(i) = .List(x)
        End If
    Next x
End With
        
        
'If values in column F do not match values in array, delete the row
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For lRow = Lastrow To Firstrow Step -1
If ActiveSheet.Range("F" & lRow).Value <> varSelected Then ActiveSheet.Range("A" & lRow).EntireRow.Delete
Next lRow
    
    
End Sub

I receive a Compile Error: Type Mismatch and it selects varSelected. What am I doing wrong in this situation?

 

[ohiojarhead]

Good Morning, I could really use some help on getting all the selected values from the listbox into an array. I then need to use the array as a variable to compare to the values in column F. This is what I have.

Private Sub CommandButton1_Click()
Dim x As Long
Dim varSelected() As String
Dim i As Integer
'Save Values of Listbox to an array
Dim SelectedItemArray() As String
ReDim SelectedItemArray(ListBox1.ListCount) As String
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        SelectedItemArray(i) = ListBox1.List(i)
    End If
Next
    
'If values in column F do not match values in array, delete the row
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For lRow = Lastrow To Firstrow Step -1
If ActiveSheet.Range("F" & lRow).Value <> ListBox1.List(x) Then ActiveSheet.Range("A" & lRow).EntireRow.Delete
Next lRow
End With
        
        

    
    
End Sub

I think I am getting closer. I just cannot figure out how to compare the value in column F to the array. I know it is probably very simple, which just adds to the frustration. Any help would be greatly appreciated.

 

[Fluff]

Hiya
Not quite sure what's happening as I posted here earlier, but it's not here!
I'm afraid I can't help with the array, but with your earlier code

If ActiveSheet.Range("F" & lRw).Value <> ListBox1.[COLOR=#ff0000]List[/COLOR](i) Then ActiveSheet.Range("A" & lRw).EntireRow.Interior.ColorIndex = 3

If you make the change in red, above, it should work

 

[ohiojarhead]

Fluff, thank you for your response. I made the update you suggested and now the code runs, I just do not achieve the desired results. Here is what I have:

Private Sub CommandButton1_Click()
Dim x As Long
Dim varSelected() As String
Dim i As Integer
'Save Values of Listbox to an array
i = i - 1
With ListBox1
    For x = 0 To .ListCount - 1
        If .Selected(x) Then
            i = i + 1
            ReDim Preserve varSelected(i)
            varSelected(i) = .List(x)
        End If
    Next x
End With
        
        
'If values in column F do not match values in array, delete the row
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For lRow = Lastrow To Firstrow Step -1
If ActiveSheet.Range("F" & lRow).Value <> ListBox1.List(i) Then ActiveSheet.Range("A" & lRow).EntireRow.Delete
Next lRow
    
    
End Sub

This goes through and deletes every row. If the user selects items from the listbox, I need to compare the selections to the value in column F. If column F contains one of the selected values in the listbox, then the row needs to remain.

 

[Fluff]

Apologies for that, I was using a single rather than multi listbox.

Private Sub CommandButton1_Click()
    Dim text As String
    Dim FirstRw As Long
    Dim LastRw As Long
    Dim i As Integer
    Dim lRw As Long
Application.ScreenUpdating = False
    For i = 0 To Me.ListBox1.ListCount - 1
       If Me.ListBox1.Selected(i) Then
           FirstRw = ActiveSheet.UsedRange.Cells(1).Row + 5
           LastRw = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
           For lRw = LastRw To FirstRw Step -1
                If ActiveSheet.Range("F" & lRw).Value = ListBox1.List(i) Then ActiveSheet.Range("A" & lRw).EntireRow.Hidden = True
           Next lRw
       End If
    Next i
    ActiveSheet.Range("A5:A" & LastRw).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Interior.ColorIndex = 3
    ActiveSheet.Cells.EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub

This may not be very pretty, but I thinks it's doing what you're after.