Loop through each item in a Data Validation list with VBA

I had an interesting question from a reader recently.  He wanted to save a PDF for each item in a data validation drop-down list.  In the past, when I have tackled similar tasks, I have always used a named range with all the possible values, then looped through the cells in the named range.

My interest was piqued, by the inclusion of the data validation drop-down list.  Below you will find my approach to looping through the list of options.

Types of data validation drop-down

Not all data validation lists are the same.  They can be based on a:

• Standard cell range
• Named range
• Comma-separated list

The screen shows below illustrate how these three methods are set up.

Cell range

Named range

Comma-separated list

The cell range and named range methods can be treated the same way from a VBA perspective, as they are both ranges. A comma-separated list is slightly different and requires an alternative approach.  The code below covers both of these options.

The VBA Code

The code below will loop through each item in a data validation list, no matter which type.

Sub LoopThroughDataValidationList()

Dim rng As Range
Dim dataValidationArray As Variant
Dim i As Integer
Dim rows As Integer


'Set the cell which contains the Data Validation list
Set rng = Sheets("Sheet1").Range("B2")

'If Data Validation list is not a range, ignore errors
On Error Resume Next


'Create an array from the Data Validation formula, without creating
'a multi-dimensional array from the range
rows = Range(Replace(rng.Validation.Formula1, "=", "")).rows.Count
ReDim dataValidationArray(1 To rows)

For i = 1 To rows
    dataValidationArray(i) = _
        Range(Replace(rng.Validation.Formula1, "=", "")).Cells(i, 1)
Next i


'If not a range, then try splitting a string
If Err.Number <> 0 Then
    Err.Clear
    dataValidationArray = Split(rng.Validation.Formula1, ",")
End If


'Some other error has occured so exit sub
If Err.Number <> 0 Then Exit Sub

'Reinstate error checking
On Error GoTo 0


'Loop through all the values in the Data Validation Array
For i = LBound(dataValidationArray) To UBound(dataValidationArray)

    'Change the value in the data validation cell
    rng.Value = dataValidationArray(i)
    
    'Force the sheet to recalculate
    Application.Calculate
    
    ''''''''''''''''''''''''''''''''''''''''''
    'Insert the code here'''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''
          
Next i

End Sub

If it is the PDF aspect which you are specifically interested in, then check out my post about saving Excel files as PDF with VBA.