How to print all options in a drop down list in Excel?
Have you ever tried to print all options in a drop-down list in an Excel worksheet? This article is talking about printing all options in a drop down list into separate printout in Excel.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
The following VBA code can help you print all options in a drop down list in the worksheet. Please do as follows.
1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, please click Insert > Module. Then copy the following VBA code into the Module window. See screenshot:
VBA code: Print all options in a specified drop-down list into separate printout
Sub Iterate_Through_data_Validation() Dim xRg As Range Dim xCell As Range Dim xRgVList As Range Set xRg = Worksheets("Sheet1").Range("B8") Set xRgVList = Evaluate(xRg.Validation.Formula1) For Each xCell In xRgVList xRg = xCell.Value ActiveSheet.PrintOut Next End Sub
Note: In the code, Sheet1 is the worksheet name which contains the drop-down list you want to print. And B8 is the cell contains the drop-down list. Please replace them with your needed.
3. Press the F5 key to run the code.
Then all options in the specified drop-down list in a certain worksheet are printed out separately.
- How to auto populate other cells when selecting values in Excel drop down list?
- How to autocomplete when typing in Excel drop down list?
- How to create a drop down list calendar in Excel?
- How to create a searchable drop down list in Excel?
- How to create drop down list with multiple selections or values in Excel?
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 6 months agoHi is there a way to be able to preview first all the possible printouts in my drop-down list before it actually prints?
- To post as a guest, your comment is unpublished.· 10 months agoHow can i select a specific droplist, i want to print? , For example if i have 200 names on my droplist and i want to print a specific list, maybe 50 out of 200, How will i do this please?
- To post as a guest, your comment is unpublished.· 10 months agoThanks for the code. But I'm experiencing a minor issue when printing. When I tried to print the sheet with the drop down list. The first print out, prints the first item on the list while the next print out prints a blank data before it prints the second item from the list. What should I do?
- To post as a guest, your comment is unpublished.· 9 months agoHi Cloud,
What version of Excel are you using?
The problem can't be reproduced in my case. Sorry for the inconvenience.
- To post as a guest, your comment is unpublished.· 19 days agoSorry I totally forgot about this. I'm using Excel 2013.
So it wasn't actually a blank data.
I have this dynamic drop down list in cell R17 which also populates other cells in the worksheet using VLOOKUP Function. What happens during print out is that the odd-positioned items on the list appears on the cell while the even-positioned items seemed not to show up that's why I thought it's printing blank pages.
- To post as a guest, your comment is unpublished.· 2 years agoThank you for the post, this has helped me a lot. I was wondering if you had a way to do VBA to print Page 1 based on one drop-down selection and print Page 1 and 2 based on another drop-down selection? Thanks for any help you can provide.