Selection.Validation in VBA

Mark Stipetic

New Member
Joined
Sep 17, 2014
Messages
1
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Watch MrExcel Video

Forum statistics

Threads
1,109,446
Messages
5,528,802
Members
409,836
Latest member
karnasrinivas
Top