How to loop through each item in Data Validation list with VBA

This post looks at how to loop through all items in a Data Validation list with VBA. This was inspired by a question from a reader. He wanted to save a PDF for each item in a Data Validation list. However, once we master the looping process, we are not restricted to PDFs; we can easily change the actions to perform for each item.

When I have tackled similar situations, I have used a Table or a named range as the source for the Data Validation list. Therefore, looping through the Table or named range achieves a similar result. However, a data validation list can also include comma-separated items; consequently, it provides a different use case.

The question piqued my interest. So, below you will find my approach.

Download the example file: Click the link below to download the example file used for this post:

Different types of Data Validation drop-down

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

• Standard cell ranges
• Named ranges
• Spill ranges
• Comma-separated lists

The screenshots below illustrate how these four methods are set up.

Standard cell range

Named range

Spill range

Comma-separated list

The cell range, named range, and spill range methods can be treated similarly from a VBA perspective; they are all ranges. A comma-separated list is slightly different and requires an alternative approach. However, the code below has been written to deal with all four scenarios.

The VBA Code

The code below loops through each item in a data validation list.

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("C2")

'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 to perform for each item here'





Next i

End Sub

Using the code for your scenario

There are two elements involved in adapting the code for your scenario:

1. The location of the Data Validation list
2. The action to perform for each item in the list.

Location of the Data Validation list

To use the code with your Data Validation list, change the following to reference the sheet and cell for your scenario.

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

Actions to perform for each item in the list

Given the context of the scenario, I believe the actions we are likely to require for this generally involve burst reporting scenarios. Therefore, the following examples may be helpful for you.

Enter the code snippets below underneath the code: ‘Insert the code to perform for each item here’.

Save as PDF

The following saves a PDF of the active worksheet for each item in the Data Validation list. Change the filename parameter to save the file into the appropriate location on your network. The selected value is used in the file name to ensure they are unique.

    'Save as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Test Folder\PDF - " & rng.Value & ".pdf"

For more examples of saving PDFs, check out our post about saving Excel files as PDF with VBA.

Send to last selected printer

The following sends the active sheet to the last selected printer.

    'Send to last selected printer
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False

Note: If it is a PDF printer, it may ask for a file name for each item. Therefore, the PDF example above may be more helpful.

Save a copy of the workbook

The following saves a copy of the workbook for each item in the list. Change the filename parameter to save the workbook in the relevant location. The selected value is used in the file name to ensure the file names are unique.

    'Save a copy of the workbook
    ActiveWorkbook.SaveCopyAs _
        Filename:="C:\Test Folder\Excel Workbook - " & rng.Value & ".xlsm"

Conclusion

For batch saving or printing individual reports based on a Data Validation list, this is a very useful technique. As it loops significantly faster than we can click, it can save a lot of time.

Here are some related posts you may find useful.

• Loop through selected sheets with VBA
• Saving Excel files as PDF with VBA
• 30 useful VBA macros – ready to use