macro to delete columns greater than specified number

[vbahelpme12]

I want to create a macro that will delete the contents in the columns greater than a specific number.

For example if I enter 3 in a cell above, I want the macro to clear the contents in columns D-O.

Right now I only have a bunch of conditional rules that can fill these with no fill and with white text but it cannot delete the actual text in the cells,

Please help!!
Thank you

[CAntosh]

Enter a 3 above what? It's hard to tell what exactly you're aiming for without a sample of your workbook, but the following code would ask the user how many columns to keep, then clear the contents from the others.

Please Login or Register  to view this content.

[vbahelpme12]

Capture.PNG

Here is a preview of the table I want to build out to how many ever columns needed. This shows it branched out into 5 columns, but I want it to be more or less based on input.

[vbahelpme12]

Level2 level2 level2 level2 level2
break break break break break
char_ char_ char_ char_ char_

[CAntosh]

I'm unable to see the pictures you've posted, which I believe is a common problem with .PNGs on this site. Can you post a sample workbook?

[vbahelpme12]

I am not sure how to post a sample workbook but here are my first 3 columns starting in the second row. This shows my table branched out to five columns, I want to be ab le to enter a # in the first row (C1) to show how many columns I want and then this information be entered in every column.


Level2 level2 level2 level2 level2
break break break break break
char_ char_ char_ char_ char_

[CAntosh]

I'm still not sure I'm following you. My current interpretation is that if Cell A1 contains the number 5, then you want the data in column A copied in 5 columns (A through E). Which would mean your request is about adding data to columns rather than deleting data from them. Correct?

[vbahelpme12]

Yes, that is correct.

[CAntosh]

If all you're looking to do is copy the data from column A to other columns, you don't need VBA. Put the following formula in B2, then fill down and right for however big your range could conceivably be. The number of columns that actually populate with data will be tied to whatever value is in A1.

Please Login or Register  to view this content.

[vbahelpme12]

I know this. However, what I am looking to do is essentially build a table out by a specified number of columns and rows based on a different input every time. So I want to be able to enter a number ( for example 10) and this table that I have created built out to 10 columns with the same data in each, or if I only enter 2, I only want it to be built out to two columns.

[vbahelpme12]

and if I only enter 2 for 2 columns, I want everything columns C on to be empty. This is to make it more intuitive for the user, for a table to be built out with their input on the number of columns.

[CAntosh]

I'm confused again, then. The formula I posted is dynamic so long as you fill it through all of the rows and columns that you might use - Fill the formula from B2:Z1000, and if you have a 2 in A1 it'll copy column A into column B. If you then change A1 to 10, it'll dynamically copy column A into columns B through J. Change it from 10 to 3 and everything right of column C disappears. How am I missing your aim?

[CAntosh]

If you're averse to formulas, here it is in macro form

Please Login or Register  to view this content.

[vbahelpme12]

Thank you, this is great and what I need except for one thing. If I already have 3 columns and decide to use this to add a new column, it changes everything I had in columns B&C to be the same as column A. When adding a new column, I want it to only copy the column right before the one added and not change the ones before them.

[vbahelpme12]

If you're averse to formulas, here it is in macro form

Please Login or Register  to view this content.

Thank you, this is great and what I need except for one thing. If I already have 3 columns and decide to use this to add a new column, it changes everything I had in columns B&C to be the same as column A. When adding a new column, I want it to only copy the column right before the one added and not change the ones before them.

[CAntosh]

Try this one. It'll copy the last used column until the number of columns specified in A1 has been filled. I removed the deleting capability that the old version had (if the number in A1 was smaller than the number of columns). Now, if you have 3 columns filled and you enter a "2" in A1, my program throws up a custom error message pointing out the problem. Will that do? I can redesign it to delete columns if A1 is too small, but I would need to know whether to delete columns starting with column A or starting with the furthest column to the right. Let me know.

Please Login or Register  to view this content.