CODEWITHSUNDEEP
validationvba
Pietro
Pietro

Reputation: 125

Using a Dynamic List Validation in VBA

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.

Upvotes: 1

Views: 6368

Answers (3)

David Zemens
David Zemens

Reputation: 53623

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

Upvotes: 0

Ateszki
Ateszki

Reputation: 2255

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

Upvotes: 1

Bruno Leite
Bruno Leite

Reputation: 1477

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.

Upvotes: 1

Related Questions