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
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:
- The location of the Data Validation list
- 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"
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
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- 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.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
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”
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.
Hi Nadine – the following posts contain the VBA code to help you work out a solution.
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
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?
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)
Formula1 is the property from the Document Object Model. It should work by changing the sheet and range name.