PDA

View Full Version : Sleeper: Select and delete



stapuff
05-24-2005, 10:20 AM
I am looking at the possibility of giving a user "select and delete options", but I am not sure of the best solution.

I have a sheet(1) column A that lists all warehouse names and column B is the associated part #'s for the warehouse.

Example

North YBAT12
North YBBU19
North ZZR321
South ACED15
South YBAT12

I am think along the lines of pivot table(ing) the part # column to give me a single list of part #'s. Then filling a listbox with the part #'s. The user can then select from the list of part #'s(multiple if needed)then hit a button to delete all occurrances of the part # and row information(via autofilter).

In the example above part # YBAT12 is located in the North and South warehouses. If a user selected YBAT12 to delete - that part # would vanish leaving the rest.

Any idea's would be greatly appreciated.

Thanks,

Kurt

MWE
05-24-2005, 03:59 PM
I am not sure I fully understand what you propose. Do you intend to actually delete information or simply not display certain information using, say, autofilter? If the latter, might it be better to call it something other than delete?

Regouin
05-25-2005, 01:40 AM
you can accomplish this with a userform with comboboxes, you only have to check for double occurances, fill up a combobox with the part numbers and then be able to select the part from a list and delete the entire row.

stapuff
05-25-2005, 06:40 AM
MWE -

It would be delete. Autofilter can be called to find the part #(s) then delete.

Regioun - Thanks for your post also. I am trying to avoid using the UF if possible.

So far to this point - I have put a listbox & commandbutton on my sheet with the following code:



Private Sub CommandButton1_Click()
Dim i As Integer
Dim Msg As String
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
If Msg = "" Then
Msg = .List(i)
Else
Msg = Msg & ", " & .List(i)
End If
End If
Next i
End With
With ActiveSheet
.Range("AH" & .Rows.Count).End(xlUp). _
Offset(1, 0).Value = Msg
End With
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next
End Sub


I have change the listbox property to multi and pointed at the pivot table with the part numbers on it.

Thanks,

Kurt