Data validation with

Paul21

New Member
Joined
Mar 17, 2018
Messages
14
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 :).

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
 

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.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
To put the DV in col K use
VBA Code:
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

New Member
Joined
Mar 17, 2018
Messages
14
hi, Fluff, it works -thx. By the wat how do you explain this Range&Range ?
"K2:K" & Range("A2").End(xlDown)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Its the range from K2 downwards until the first blank cell in col A.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,332
Messages
5,624,053
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top