Excel Macro: Select cells on condition and use as data validation list

Question

Hi,

I'd like to select all cells (except the first one) from column A of sheet B that are not empty, and use it as the list for data validation on a range of sheet A. I already have code to add the validation:

Cells.SpecialCells(xlCellTypeFormulas).Offset(0, 1).Select
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=**The range i need**"
    .IgnoreBlank = True
    .InCellDropdown = True
 End With

I'm not able to make it work on excel 2003. In excel 2003 it's not possible to add a reference to another sheet for data validation

Thank you!

Answer 1

I've never run into a problem crossing sheets with Excel 2003.

Your description is confused as to what's where, so here's a guess at some code you can play with to make it work. Things will go a lot faster if you start by loading your list into an array and work with it from there, but the "vrange" will give you the range for the list.

Put a heading and stuff into column A of a sheet named "B" and this code should give you what you need.

Option Explicit

Public Sub test()
    Dim vlist As Variant
    Dim vrange As Range
    Dim i As Variant

    '' find the range, eliminate header
    Set vrange = Range("B!a1")
    Set vrange = vrange.Range("A2", vrange.End(xlDown))

    '' copy into an array
    vlist = vrange.Value

    '' Show what we got
    Debug.Print vrange.Address(external:=True)
    For i = 1 To UBound(vlist, 1)
         Debug.Print vlist(i, 1)
    Next i
End Sub

Answer 2

If you want to have your validation list cross worksheets, you need to define a named range for the validation data. In Excel 2003 (if I recall correctly) you define name ranges in the Insert > Named ranges > Define. If you want to assign the validation in code you just use the name of your range as the address.

Cells.SpecialCells(xlCellTypeFormulas).Offset(0, 1).Select
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=TheNameOfYourRange"
    .IgnoreBlank = True
    .InCellDropdown = True
End With

Of course you probably won't have to do this in code - you'll only have to define the validation rule once, then update the named range from that point on. To update the named range to refer to non-blank cells on a particular worksheet, you can use something like this.

Dim addresses As Variant

addresses = Split(Sheets("Other sheet").Range("A2:A9999") _
        .SpecialCells(xlCellTypeConstants).Address, ",")
Names("TheNameOfYourRange").RefersTo = _
    "='Other sheet'!" & Join(addresses, ",'Other sheet'!")