This post may contain affiliate links. Please read my disclosure for more info.

Loop through each item in a Data Validation list with VBA

VBA Code Snippets

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

Data Validation Drop Down - Cell Range

Named range

Data Validation Drop Down - Named Range

Comma-separated list

Data Validation Drop Down - 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.



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

2 thoughts on “Loop through each item in a Data Validation list with VBA

  1. Nadine says:

    Hello,

    How can I loop through data in an excel spreadsheet and grab each cells data value one at a time? Then, I want to check to see if it is in a temperature range and then output to a new worksheet. My worksheet has 21 columns and 400 rows.

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *