Changing drop down range

[Naveen N]

Dear Reader,

To add a drop down list, we select a range (example A1 : A10) and add a drop down list.

If I need to change the range – like A1 to A100, please suggest the best way to do it. Since I need to do it multiple sheet and multiple columns.

Regards,

Naveen N

 

[Villalobos]

Hi,

Create a name by Name Manager and put the following formula in the Refers to box: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1). And use this name as data validation source.

 

[Naveen N]

Hi Villalobos,

Thanks for the updates. Sorry for the delayed response.
When I have selected A1:A10, the drop down will be present only for the selection A1 to A10.

If I need to change the selection of drop down from A1:A100, please suggest the best way to do it automatically.

Regards,
Naveen N

 

[Villalobos]

Hi,

I attached a sample file.
You can find in column A some fruits, I created a defined Name by Name Manager(called: Listofculinaryfruits) with the following formula that will give you a dynamic list with the exact count: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1).
If you want to add more fruits to the list then you will see them in the drop down list (in cell B2).

Check it out.

 

[Naveen N]

Hi,

Thanks for the sample file. It helps to get the answer.

In the sample file, the drop down is present in B2, if I need to increase the drop down from B2 to B100, please suggest the best way to perform the same. I am aware we can select B2 to B100 and Data validation provide the name (as in Name Manager).

I have many columns where I need to update the range (B2 to B100). Please suggest the best way to update the same.

Regards,
Naveen N

 

[Villalobos]

Hi,

Try this approach. In the sample file there are 2 defined names and you can set the data validation by userform.

0. step - Press the SHOW USERFORM button
1. step - Select the Defined Name in the combobox
2. step - Set the range of data validaton in textboxes (i.e. from C1 to C2)
3. step - Press the OK button

I hope this help for you.

Option Explicit
Private Sub UserForm_Initialize()
Dim DefinedNames As Name

  For Each DefinedNames In ThisWorkbook.Names
  ComboBox1.AddItem DefinedNames.Name
  Next DefinedNames

End Sub
Private Sub CommandButton1_Click()
Dim Target As Range
Dim a As Variant

  If UserForm1.Controls("ComboBox" & "1").Value = "" Or _
  UserForm1.Controls("TextBox" & "1").Value = "" Or _
  UserForm1.Controls("Textbox" & "2").Value = "" Then
  MsgBox "Missing fields!", , "Error message"
  Exit Sub
  End If

  a = TextBox1 & ":" & TextBox2

  Set Target = ActiveSheet.Range(a)

  With Target
  .ClearContents
  With .Validation
  .Delete
  .Add Type:=xlValidateList, _
  AlertStyle:=xlValidAlertStop, _
  Formula1:="=" & ComboBox1
  End With
  End With

  Unload Me

End Sub
Private Sub CommandButton2_Click()
  Unload Me
  Application.ScreenUpdating = False
End Sub

 

[Naveen N]

Thanks you very much, This helps to resolve the issue.

 

[Villalobos]

I'm glad I could help to you.
The part of your "thank you msg" goes to @SirJB7... he is inactive since many months but anyhow... may be he see this