Data validation with

[Paul21]

Hallo,

I am Paul and I am trying to get drop list (data validation) in every line up to the last not empty line using drop list values from the same line.
For example - droplist in K2 with values from B2 to I2. This should be aplied to all not empty lines.

This is my code wich let me have drop lists up to the last non empty line (Range("A2", Range("A2").End(xlDown)).Validation) although I would like to have this in collumn K and not A.
The most tricky part is that I do not find a way how to modifu this (Formula1:="=Sheet2!B3:I3") in order to update/iterate for every line down to the last non empty cell.
Would be very gratefull for help .

Sub droplist()

'replace "J2" with the cell you want to insert the drop down list
With Range("A2", Range("A2").End(xlDown)).Validation
.Delete
'replace "=A1:A6" with the range the data is in.
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Sheet2!B3:I3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With
End Sub

 

[Fluff]

To put the DV in col K use

With Range("K2:K" & Range("A2").End(xlDown).Row).Validation

Assuming the DV should look at the values in that row, the formula should be
=Sheet2!B2:I2

 

[Paul21]

hi, Fluff, it works -thx. By the wat how do you explain this Range&Range ?
"K2:K" & Range("A2").End(xlDown)

 

[Fluff]

Its the range from K2 downwards until the first blank cell in col A.

 

[Paul21]

Its the range from K2 downwards until the first blank cell in col A.

Very gratefull for explanation.

 

[Fluff]

You're welcome.