Input Box prompt

[cornepiek]

Hi all

i have an input box prompt to enter a value to delete.

however, the datasheet varies and can be up to a couple of thousand rows long.
so it is difficult to type the exact value to delete.

in column A i will have a customer name repeating for each change in column B.


so i will have
A2 - Mobifin B2 - National Calls
A3 - Mobifin B3 - Local Calls
A4 - Mobifin B4 - Cell Calls
A5 - Maxtel B5 - National Calls
A6 - Maxtel B6 - Local Calls
A7 - Maxtel B7 - Cell Calls


is it possible to have the input box prompt have a dropdown with all the various names in column A.
the idea is to have a dropdown containing

Mobifin
Maxtel.

then i can choose what i need to have deleted without making spelling mistakes

 

[wigi]

Found with a Google search action:

http://www.exceltip.com/st/Fill_a_L...rksheet_using_VBA_in_Microsoft_Excel/409.html

 

[cornepiek]

thanks. i also cam across this one, but im bit of a noob with VBA.


here is my vba macro.

the macro will delete all rows, where the value of column a does NOT contain string from input box

Sub delete_It()

Sheets("Sheet1").Select

Dim MyRange1 As Range
Dim MyRange As Range
Dim strToDelete As String

strToDelete = InputBox("Value to Trigger Keep", "Delete Rows")

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A2:A" & lastrow)
For Each c In MyRange
If InStr(1, UCase(c.Value), strToDelete, 0) = False Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If

End Sub


i just need to add the above script to make the input box display the list