Solved: Deleting source data used for drop down list?

[jetimmins]

Heyya,

I have a column of data which is the source for my drop down list, i.e

A
-
Apples
Oranges
Limes
Old Man
Grapefruit


I want to be able to delete the source data that I have selected from my drop down list with a vba button, so e.g

- Limes selected from drop down
- Button Pressed
- The row with Limes on in the list source data is deleted, in this case it's row 3

Any ideas on the macro for the button that would do this?

Thanks for any help <3

[Bob Phillips]

If you just don't want an entry to be re-used, try this technique http://www.contextures.com/xlDataVal03.html

[jetimmins]

That's some interesting stuff, and thank you, but I need to actively delete entries from the list with the button, it's for a save-like function. Just some VBA that will look at the value in cell whatever, find the value in my named range and delete that row?

[Bob Phillips]

You should be able to adapt this

[vba]

Dim cell As Range

With ActiveSheet

Set cell = .Range("DVList").Find(.Range("DVCell").Value)
If Not cell Is Nothing Then

cell.EntireRow.Delete
End If
End With
[/vba]

[jetimmins]

Thanks, I think I got something working in the end:

[VBA] Application.ScreenUpdating = False

Worksheets("Saved Orders").Select
Range("SavedOrders").Select
For Each cell In Selection
If cell.Value = Worksheets("PMU").Range("M68").Value Then
cell.EntireRow.Delete
End If
Next cell
Range("a1").Select

Application.ScreenUpdating = True[/VBA]

Yours looks a lot neater though, I might play around with that instead

[Bob Phillips]

Find is quicker than a loop.