Excel - Creation of dropdown from dynamic list in VBA

Ask a question


I have been trying to make a Macro that can create a dropdown list from a dynamic list in column A. So far I've come up with the following code:

Sub Test() 
Range("A1", Range("A65536").End(xlUp)).Name = "MyList" 
Cells(1, 3).Select 
With Selection.Validation 
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
xlBetween, Formula1:="= & MyList" 
.IgnoreBlank = False 
.InCellDropdown = True 
.InputTitle = "" 
.ErrorTitle = "" 
.InputMessage = "" 
.ErrorMessage = "" 
.ShowInput = True 
.ShowError = True 
End With 
End Sub 

However calling MyList doesn't work.


Try this:
  • Go to data validation, choose list and type =MyList
  • Now right click on the sheets tab and select view code and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
Dim lRow As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & lRow).Name = "MyList"
End Sub
  • This code will change the range of MyList whenever a change is made in column A.

Thanks to TrowaD for this tip.

Excel - Average multiple cells in multiple sheets
Excel - A macro copy to cell based on a specific criteria