I want to write a macro such that it add a validation list based on a selection in a cell lying immediately before selection. For example, if I selected A101:A103, This macro would add a validation list in A100, including values of A101:A103. I have written following macro, but it doesn’t lead to my expected result. (It add a validation list in A100, including just “range01:range02". In the other words, the macro treats range01 and range02 as texts not parameters). Can anybody guides me on this issue?



Sub exdatalist()

Dim range01 As Variant
Dim range02 As Variant
Dim n As Variant
n = Selection.Rows.Count
range01 = Selection.Rows(1).Address
range02 = Selection.Rows(1).Address


Selection.Rows(1).Offset(rowOffset:=-1, columnOffset:=0).Activate

With ActiveCell.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="range01:range02"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


End Sub