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]

Fluff, thank you so much! I modified it a little to suit my needs. Here is the final:

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 + 1
           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("A2:A" & LastRw).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Interior.ColorIndex = 3
    ActiveSheet.Cells.EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub

Thanks again!!!

 

[Fluff]

Glad to help & thanks for the feedback.
Further thought, if you're running 2007 or newer you might want to look at autofilter. In 2003 you can only use 2 criteria, but I believe that has been increased. If so you may be able to pass your .List(i) results into a filter, rather than looping through the data multiple times.

 

[Niketa]

Hi,
I have a multiselect list box that is for the different activities and I want the Activity names (Columns headers on the worksheet) to be selected and rest of the columns to be deleted from my worksheet only.
Please help.

Niketa

 

[ohiojarhead]

Niketa, that is a separate issue and should be posted in a new thread. I am not sure if it would work, but I would try using the code above and substitute .row for .column