This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The first method uses an Excel defined Table.
The benefit of using a drop-down list in an Excel defined Table is that the whole table column contains drop-down lists automatically, it will also expand automatically if more values are added to the Table meaning you don't need to copy and paste drop-down lists.
You only need to copy a drop-down list and paste once to a cell in the Table column and Excel will instantly fill the remaining column cells with the same drop-down list.
The second method is an event procedure that makes sure there are drop-down lists in column A if there are adjacent values in column B and C.
The data validation lists contain values from a named range. The named range (E2:E8) expands when you add new values to the list.
The animated gif shows you that. The animated gif below also shows you when adding a new company name in cell B11, a drop-down list (Data validation list) is instantly applied to cell range A2:A11.
The VBA code in this sheet and a named formula make it all happen!
Let me explain how I created this sheet, instead of using an Excel defined Table I created a named range that expands. I have created comments to the VBA code I created, you can find it further down in this article.
'Event code that runs if a cell value changes.
'The Target argument contains the cell address that changed.
Private Sub Worksheet_Change(ByVal Target As Range)
'Dimension variables and declare data types
Dim Lrow As Single
Dim AStr As String
Dim Value As Variant
'Check if Target cell is located in column B or C or if a cell value changed in column E.
If Not Intersect(Target, Range("$B:$C")) Is Nothing _
Or Not Intersect(Target, Range("E:E")) Is Nothing Then
'Save last non-empty cell row in column B to variable Lrow
Lrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
'Add values in named range Region to string variable AStr
For Each Value In Range("Region")
AStr = AStr & "," & Value
'Populate a cell range in column A with drop-down lists, based on the number of rows in variable Lrow
'The drop-down lists contain values from string variable AStr
With ActiveSheet.Range("A2:A" & Lrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True