Using a Dynamic List Validation in VBA

Question

I'm new to programing in VBA and I'm trying to validate data with a list in an Excel worksheet. The problem is the list varies in size every time I select a different criteria from a drop-down list.

For example: when I select China, the list turns into 10 different sellers. Range A1 to A10, but when I select Japan I only have 5 sellers, from A1 to A5.

So I need a new range in the the Formula1 part each time.

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=$Z$1:$Z$30"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

What would be the best way to do this?

I know if I leave a fixed range it can work but it doesn´t look OK, because it leaves a lot of empty spaces and it doesn't look neat.

I hope it´s understandable.

Ateszki · Accepted Answer · 2013-04-24 18:17:32Z

1

You can get the las non empty cell of the column with something like this

Worksheets("Sheet1").Range("A1").End(xlDown)

Then you just build your Formula1 properties from A1 to the result.

Dim strFormula1 as string
strFormula1 = "=$A$1:" & Worksheets("Sheet1").Range("A1").End(xlDown).Address()

Hope it helps, have not tested might have errors

edited Apr 24, 2013 at 18:17

answered Apr 24, 2013 at 18:09

Ateszki

2,2531 gold badge16 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bruno Leite · Accepted Answer · 2013-04-24 18:09:38Z

1

Use a Named Range with formula

To create a Name go to Formula / Name Manager / New

Choise a name, for example DataValidation, in RefersTo use

=OFFSET(Sheet1!$Z$1,0,0,COUNTA(Sheet1!$Z:$Z),1)

Now, you have a dynamic interval , and can use on your validation.

answered Apr 24, 2013 at 18:09

Bruno Leite

1,47713 silver badges17 bronze badges

Comments

David Zemens · Accepted Answer · 2013-04-24 18:24:35Z

0

The other two answers are simpler, but will not work if you ever have non-continous non-blank cells that need to be in the validation list. This method should overcome that :)

You could use a custom function in VBA to return the filtered string address. This will return the filtered address, or it will return the original address IF the filtered address is not a valid range.

NOTE This will likely fail if the returned address exceeds the 255-character limit.

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=GetAddress(Range("$Z$1:$Z$30"))
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Place the function in a normal code module.

Function GetAddress(myRange As Range) As String

Dim cl As Range
Dim c As Long: c = 1
Dim tmpAddress As String

For Each cl In myRange
    If cl.Value <> vbNullString Then
        'Create a string value of cell address matching criteria'
        If tmpAddress = vbNullString Then
            tmpAddress = myRange.Cells(c).Address
        Else:
            tmpAddress = tmpAddress & "," & myRange.Cells(c).Address
        End If
    End If
    c = c + 1
Next

If Not Range(tmpAddress) Is Nothing Then
    GetAddress = "=" & tmpAddress
Else:
    MsgBox "There are no non-empty cells in this range.", vbInformation
    GetAddress = "=" & myRange.Address
End If

End Function

edited Apr 24, 2013 at 18:24

answered Apr 24, 2013 at 18:09

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

3 Comments

Pietro Over a year ago

I'm sure your answer works perfect, but it´s to advanced for me :)

David Zemens Over a year ago

No problem. The only thing this method can do which the others cannot, is that it allows your range to contain blank cells anywhere. The other methods require that the range end at the very first blank cell. If that's the extent of your problem, then the other answers are perfectly acceptable. Cheers!

Pietro Over a year ago

Exactly, in my case I will not have that problem, but I'll keep yours in mind for the future. Thnx again.

Collectives™ on Stack Overflow

Using a Dynamic List Validation in VBA

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related