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:
Range("A1", Range("A65536").End(xlUp)).Name = "MyList"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="= & MyList"
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
However calling MyList doesn't work.
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"
This code will change the range of MyList whenever a change is made in column A.