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

Posted on 2011-10-25
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
    LVL 92

    Accepted Solution

    The easiest thing to do would be to add it to the whole column at the outset.
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    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

    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 100

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    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 will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now