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 posts:

Apply data validation lists dynamically (vba)

Dependent data validation lists in multiple rows

How to use a table name in data validation lists and conditional formatting formulas

Search for multiple text strings in multiple cells and use in data validation in excel

Automate data entry (vba)