Insert Column on criteria of text input to cell

IS it possible to automatically insert a new column immediately to right of a cell on condition of that cell having data inputted.

In essence, I want to expand my data table by 1 column each time data is input to the last column so there is always a 'fresh' column on the end waiting for new data.

Hope this makes sense.

Any help would be appreciated.

Rick

Hi Rick,

Insert the following code in the VBA module of the worksheet in which you want to provide the "Add Column" feature:

Please Login or Register  to view this content.

Any time a cell's value changes from a blank to a non-blank value, a new column will be inserted to the right of the cell whose value has just changed. It's necessary to include the "blank to non-blank" check, otherwise simply EDITING an existing value in the middle of the data row would cause a new column to be inserted (also in the middle of the data row!)

I feel you'll probably need to define a worksheet range outside of which this feature will NOT apply - if not you might end up adding new columns left, right and centre! Let me know if you need help in restricting this feature.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

Yes. That works great Greg.

This is my first venture into the VBA module. It seems like a whole new world of possibilities!

You mentined that it would be useful to restrict this 'new column' code to a specific range. Would it be possible for the code only to work if data is input into any cell on row 102?

I look forward to finding out!

Rick

Also, is there a way of making it work on a protected sheet or is that asking too much!!!

Rick

Hi again Rick,

Yes to both questions - I'm in a rush right now, but I'll get back to you with solutions (today, I hope).

Is your worksheet password-protected? If so, the password needs to be included in the code. Let me know if you're using a password, you don't need to tell me what it is - I'll just insert a dummy password which you can change to the correct version.

Regards,

Greg M

Hi Rick,

Ok - back again!

Insert the following code in the VBA module of the worksheet where you want to provide the "Add Column" feature:

Please Login or Register  to view this content.

where
strPassword is the password for protecting the worksheet, and
lngDataRow is the row in which you want the "Add Column" feature to apply.

You can assign the appropriate values to these constants.

Hope this helps - please let me know how you get on.

Best regards,

Greg M