Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

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

VBA Code Snippets

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

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
    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
    '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, _

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"


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.

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

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

  1. Nadine says:


    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.


  2. Jay says:

    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

  3. cc says:

    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?

  4. Suci says:

    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)

Leave a Reply

Your email address will not be published.