I have been trying to make a Macro that can create a dropdown list from a dynamic list in Rows 1-100. So far I've come up with the following code:
This code is entered in the corresponding sheets 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"
While this code is entered in a module:
Range("A1", "A" & ).Name = "MyList"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="= & MyList"
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True