Excel: Apply Data Validation on the fly with VBA/Macros

Posted on 2011-10-25
Medium Priority
Last Modified: 2012-05-12
i usually add data validation (list/dropdown) manually on the cell, the problem arise if i enter data on new row, i have to copy data validation cell in the previous row , and paste in current row.

as i would like to pass this excel to my co-worker, i need the data validation automatically applied in specific column.

How to achieve this ?
Question by:veematics
  • 2
LVL 93

Accepted Solution

Patrick Matthews earned 1336 total points
ID: 37028986
The easiest thing to do would be to add it to the whole column at the outset.
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1336 total points
ID: 37028993
Another option, if you are using Excel 2007/2010:

Create a Table, and then when you add a new row to the bootm, the table automatically resizes to cover the new row, and will copy down the Data Validation settings.
LVL 11

Assisted Solution

ScriptAddict earned 664 total points
ID: 37032996
You can also add some vba code that will make sure that all rows with data have the formula in that column.  Let me know if you want something hammered out.  
LVL 101

Expert Comment

ID: 37181755
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question