Cell B3 contains a drop down list (Data Validation). The values in drop down list are from column H. I am using a dynamic named range.

Enter a value in cell E3 and press "Add" button to add the value to the list in column H. You can also remove a value using the "Remove" button.

See the animated gif below.

Create a named range

  1. Go to tab "Formulas"
  2. Click "Name Manager" button
  3. Click "New.."
  4. Enter List in Name field
  5. Enter formula in Referes to: field:
    =Sheet1!$H$3:INDEX(Sheet1!$H$3:$H$1000, COUNTA(Sheet1!$H$3:$H$1000))
  6. Click Close button

Explaining named range formula

Step 1 - Count the number of cells in a range that are not empty

 COUNTA(Sheet1!$H$3:$H$1000) returns 4.

Step 2 - Return a cell reference to  a single cell

INDEX(Sheet1!$H$3:$H$1000, COUNTA(Sheet1!$H$3:$H$1000))

becomes

INDEX(Sheet1!$H$3:$H$1000, 4)

and returns

$H$6

Step 3 - Create a cell reference to a cell range

=Sheet1!$H$3:INDEX(Sheet1!$H$3:$H$1000, COUNTA(Sheet1!$H$3:$H$1000))

returns =Sheet1!$H$3:$H$6

That is the cell reference to all the values in column H.

Populate drop down list

  1.  Select cell B3
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Click "Data Validation..."
  5. Select List in "Allow:" field
  6. Type =List in source: field
  7. Click OK.

Add vba code

  1. Press Alt + F11
  2. Right click on your workbook in the project explorer
  3. Click "Insert" and then "Module"
  4. Paste macros to code module
Sub AddValue()
Dim i As Single
i = Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row + 1
With Worksheets("Sheet1")
.Range("H" & i) = .Range("E3")
.Range("E3") = ""
End With
End Sub

 

Sub RemoveValue()
Dim i As Single
Dim Cell As Range

i = Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row
Set Cell = Worksheets("Sheet1").Range("H" & i)

Do Until Cell.Row = 2
    If Worksheets("Sheet1").Range("E3") = Cell And Cell <> "" Then
        Cell.Delete Shift:=xlUp
        Worksheets("Sheet1").Range("E3") = ""
        Exit Sub
    End If
    Set Cell = Cell.Offset(-1, 0)
Loop

MsgBox "Can´t find value: " & Worksheets("Sheet1").Range("E3")

End Sub

Assign macros to buttons

  1. Go to tab "Developer"
  2. Click "Insert" button
  3. Select "Button" (form control)
  4. Create a button
  5. Enter button text: Add
  6. Assign macro AddValue() to button

Create a remove button and assign RemoveValue() macro.

Download excel file

Add or remove a value in a drop down list.xlsm