Inserting column adopts data validation from column to the left - how to stop this?

Excel has a 'helpful' function - when you insert a new column it adopts the formatting and data validation from the column to the left.

When you insert a cut/copied column that contains its own formatting and dropdowns, the inserted column retains its formatting but still adopts the dropdowns from the column to the left, even overriding the dropdowns that were in the inserted column.

Does anyone know how to stop this from happening, and just let the cut/copied column be inserted and keep its dropdowns?!

Originally Posted by tanasedn

Hi,
I don't know to be possible to change the INSERT properties without VBA but you can use the copy with insert in the same time using CTRL+SHIFT+drag
Select the cells that you want to cut but use the method above it will preserve the validation rule

Thank you! I actually am running this through VBA, using entirecolumn.insert to insert the cut column. Do you have a VBA solution? Thanks again!

Thank you for your reply. I found a weird workaround - if I CUT-insert then I have the problem. If I COPY-insert then it is solved. So I modified my code to copy-insert, then deleted the original column. It is fixed. So weird!

I found a weird workaround - if I CUT-insert then I have the problem. If I COPY-insert then it is solved. So I modified my code to copy-insert, then deleted the original column. It is fixed. So weird!