PDA

View Full Version : Solved: Deleting source data used for drop down list?



jetimmins
01-03-2012, 09:52 AM
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
01-03-2012, 10:52 AM
If you just don't want an entry to be re-used, try this technique http://www.contextures.com/xlDataVal03.html

jetimmins
01-04-2012, 02:04 AM
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
01-04-2012, 03:59 AM
You should be able to adapt this



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

jetimmins
01-04-2012, 04:05 AM
Thanks, I think I got something working in the end:

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

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

Bob Phillips
01-04-2012, 04:44 AM
Find is quicker than a loop.