Selection.Validation in VBA

[Mark Stipetic]

Hi,

I'm trying to create a Macro to apply dropdown validation to a cell.
i have
Column M the data entry cell to apply the validation to
Columns N onwards contain the values.

so far i have ...

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+w
'
Dim sTemp As String

sTemp = "=$N$" & ActiveCell.Row() & ":$ZZ$" & ActiveCell.Row()
'MsgBox (sTemp)
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=sTemp
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

but would like the end of the validation to be the last one containing data (as if going to column N and doing End then Right) rather then just ZZ+CurrentRow()

any ideas?

as a secondary question, dropdowns seem to position at the end of the list, is there any way to get it start at the top?

Thanks
Mark