Macro to control number of rows that data validation drop down lists are copied down to

Is it possible to have a macro that will control the number of rows that data validation drop down lists are copied down to in a worksheet?


More info:
I have a sheet that contains data that goes down as far as row 400 and includes some columns with drop down lists copied down also as far as row 400. However during use, the user will delete any number of the rows above as data become obsolete and so (unless I am mistaken) this means that they will eventually start adding data into rows that don't have data validation.

I would like a macro that automatically ensures that cells down as far as row 400** always have the appropriate data validation lists copied down for that column

**(ideally this number, i.e. row 400, will also be controlled by using some kind of indirect reference, taking the number '400' as entered in a cells of a another sheet I have which is currently controlling the bottom row cell reference for various named ranges through an indirect function.)


Thank in advance for any assistance!

Hi OLLY-7,

I approach your problem from a slightly different point of view to keep the coding a little simpler by:
a. Refreshing the 'Data Validation' on Worksheet_Activate()
b. Doing a 'Data Integrity' Check and reporting to the user any cell containing 'Data Validation' (after being refreshed) whose value is NOT in the 'Data Validation' List.

I hope this helps.

Lewis

Dear all,

Thank you very much for your quick responses.


dflak - I have looked into Excel Tables (I am actually running 2016 now actually) and yes these look like they would do exactly what I want... I.e. named ranges will reduce when rows are deleted but will also increase again when data is added, even if at the bottom of the table (i.e. the table auto extends) and yes you are right - the data validation also auto populates to these new rows!

I think maybe this will also speed up the calculation on my workbook as the named ranges will only ever be as long as they need to be and will not be using the indirect function to recalculate the range lengths (although that does work and I was quite proud of myself for working that one out!).


Mumps1 - thank you for your thinking about this. If you have time, might be interesting to see what is possible re. a macro to force data validation lists to always be 'copied' down to a specified row number, no matter how many rows are deleted above. But then again, you may also agree with the Excel Table solution(?).


LJMetzger - I don't entirely follow what you are suggesting but sure it’s good! I'm not a macro wiz (can record and adapt a bit existing code but that's about it alas).


I attach some stripped out workbooks with the two alternate routes should these be of use / interest.


Thanks very much again in advance.


Olly