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 .
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 .
VBA Code:
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