Userform Listbox selections to delete rows from worksheet

ohiojarhead

New Member
Joined
Jan 23, 2014
Messages
28
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

ohiojarhead

New Member
Joined
Jan 23, 2014
Messages
28
I managed to put this together.
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,203
Office Version
  1. 365
Platform
  1. Windows
Hi
Your missing this
Code:
        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

New Member
Joined
Jan 23, 2014
Messages
28

ADVERTISEMENT

Thank you! I changed it to this:
Code:
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

New Member
Joined
Jan 23, 2014
Messages
28
Sorry to keep updating this thread, but I think I am getting close. I now have this:
Code:
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

New Member
Joined
Jan 23, 2014
Messages
28

ADVERTISEMENT

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.
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,203
Office Version
  1. 365
Platform
  1. Windows
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
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

New Member
Joined
Jan 23, 2014
Messages
28
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:
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,203
Office Version
  1. 365
Platform
  1. Windows
Apologies for that, I was using a single rather than multi listbox.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,987
Members
409,614
Latest member
wile2u

This Week's Hot Topics

Top