In cell validation using non-contiguous source range

A

arthur_etchells

Board Regular
Joined
May 15, 2004
Messages
169
Basically, I need the validation box to have as its source array a non-contiguous range of cell values. I couldn't get the validation list to take the non-contiguous range when entered in the formula1:= area, so I figured a named range would do the trick. However, I get an error at the validition.add line. The example below is a simplified mockup of the final version of the code, as the final version will need to draw on a larger number of cells.

Code: 
Private Sub FillPickConditionFields(sh As Worksheet)
    sh.Cells.Validation.Delete
    ActiveWorkbook.Names.Add Name:="srtRng", RefersTo:="=Blur sheet!$A$4,Blur sheet!$E$4:$I$4"
    With sh.Range("C1").Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=srtRng"
        .InCellDropdown = True
    End With
End Sub

Am I missing something here? Is it possible to use non-contiguous ranges in a validation drop-down like this?

Thanks for any help,
Arthur
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi Arthur,

I don't believe you can fill the validation list with a reference to a discontiguous range--an unfortunate Excel limitation. However, if the total length of characters in all the list cells is less than 255 minus the number of cells, you can change your code a bit to load the list as a string of comma-delimited literals.

If longer than 255 you will have to switch to using an in-cell combobox and loading its list using the AddItem method since I believe listbox and combobox controls have the same limitation regarding discontiguous ranges.

If you are interested in code for either of these, let me know.

Damon
 
Upvote 0
Hi again Arthur,

Like this:

Code: 
Private Sub FillPickConditionFields(sh As Worksheet)
   Dim ValList       As String
   Dim c             As Range
   sh.Cells.Validation.Delete
   For Each c In sh.Range("A4,E4:I4")
      ValList = ValList & c.Text & ","
   Next c
    sh.Cells.Validation.Delete
    With sh.Range("C1").Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ValList
        .InCellDropdown = True
    End With
End Sub

Note that it is not necessary to name the range.

Keep Excelling.

Damon
 
Upvote 0
Just have a try with the following:

Code: 
Private Sub FillPickConditionFields(sh As Worksheet)
Dim i As Integer, s As String
sh.Cells.Validation.Delete
ActiveWorkbook.Names.Add Name:="srtRng", RefersTo:="=Blur sheet!$A$4,Blur sheet!$E$4:$I$4"
For i = 1 To Range("srtRng").Cells.Count
s = s & "," & Range("srtRng").Cells(i)
Next
With sh.Range("C1").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Mid(s, 2)
.InCellDropdown = True
End With
End Sub
 
Upvote 0
Hmmm. Just tried to run the code and got an error at the line:
Code: 
For i = 1 To Range("srtRng").Cells.Count

Method 'Range' if object '_Global' failed
Click to expand...

Any idea what the problem is here? I tried the code with ActiveWorkbook and ActiveSheet added to the beginning of the above range reference, to no avail. Suggestions?
 
Upvote 0
Hi,
perhaps some help
Thanks for the help! One question: what is the role of mid() in this code?
Click to expand...
check out the helpfiles what MID is doing
three argurments
text, start, length
if the last argument is not there it is assumed "to the end"
mid(s,2) is "everything from second character

but did you test Damon Ostrander's code ?
works nicely for me
and as he points out: no need to name the range

kind regards,
Erik
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question
recorded macro throwing 1004 error
Replies
3
Views
361
ursua
U
D
  • Solved
Error 1004 when trying to set validation
Replies
1
Views
381
Dean76
D
J
  • Question
Combining VBA code together...need help!
Replies
3
Views
150
Fluff
Fluff
N
  • Solved
VBA Question
Replies
3
Views
117
RoryA
RoryA
G
  • Question
VBA - add formula to cell when created
Replies
2
Views
286
Golaidron
G

Forum statistics

Threads
1,214,628
Messages
6,120,618
Members
448,973
Latest member
ChristineC

Share this page

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
Back
Top