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.

Table of Contents

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0119 Looping through Data Validation list.zip

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

Data Validation standard cell range

Named range

Data Validation named range

Spill range

vData Validation spill range

Comma-separated list

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


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

6 thoughts on “How to loop through each item in Data Validation list with VBA”

  1. 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!

    Reply
  2. Hi Excel off the Grid, I would be able to save so much time if I combined the above script with a part that includes saving each of the data validation options as a separate PDF in the same folder as the Excel file (naming it the name of the data validation option). I checked out your other feature on Saving Excel Files as a PDF, but I am not proficient enough to combine the two into a usable script. Can you please assist? Thank you so much

    Reply
  3. I have 5 worksheets set up to one data validation list in cell A6. I need to print each one of these as a PDF or preferably an Excel workbook. Could someone assist me with this code?

    Reply
  4. hi thankyou for your code, but what do you mean with Formula1? should i replace the formula1? i tried with only change my sheet dan range name but i only got 0.pdf. please help thankyou

    ‘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)

    Reply

Leave a Comment