In this tutorial I am going to show you how to create a drop down list (data validation) in cell C2.

When a value is added, changed or deleted from column A, the drop down list is instantly refreshed.

Explaining the vba code below

  1. Find the row number of the last cell value in column A.
  2. Concatenate all values in cell range into a string.
  3. Add string to drop down list

VBA code

Where to copy the code?

  1. Copy code below
  2. Press Alt+F11
  3. Insert a module
  4. Paste code into code window
  5. Return to excel
Sub AddData()
Dim Lrow As Single
Dim AStr As String
Dim Value As Variant

Lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For Each Value In Range("A1:A" & Lrow)
    AStr = AStr & "," & Value
Next Value

AStr = Right(AStr, Len(AStr) - 1)

With Worksheets("Sheet1").Range("C2").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=AStr
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

End Sub

Explaining the vba code below

This code runs subroutine AddData whenever a cell in column A is changed.

Where to copy the code?

  1. Copy code below
  2. Press Alt+F11
  3. Double click sheet1 in project explorer
  4. Paste code into code window
  5. Return to excel
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("$A:$A")) Is Nothing Then
    Call AddData
End If

End Sub

Download excel file

Add values to a data validation list.xlsm

Related articles