If column contains certain header, delete column--exempt certain columns

**kestefon** · 04-18-2013, 10:20 AM

Hi,

I frequently have to format reports in which numerous columns with the same headers need to be removed. For instance, my worksheet contains dozens of columns with the headers like "Detail X", "Detail Y", etc. and I want to quickly delete all of those columns.

And there's a catch here: In some case I'd like to be able to keep a certain "Detail X" or "Detail Y" column. i.e. Delete all Detail X columns except for column 5.

I'm wondering if some type of userform would be ideal here? For instance: Enter the header label for columns you'd like to find and delete, and then enter any exemptions. Enter the next header label for columns you'd like to find and delete ,and then enter any exemptions. And so on and so forth.

This might be a time-consuming thing to build, so I'm not asking anyone to do that. I'm just looking for any suggestions or ideas about how to accomplish this.

Some specific questions:
Would a userform be the most convenient way to do this?
In terms of selecting the columns, I'm thinking I would Find the header, select the column, delete, then loop; is that the most efficient way, or could I actually select all of the columns all at once?

**stnkynts** · 04-18-2013, 11:02 AM

A user form would be one way to do it. But like you indicated, it would take a fair amount of time and there may not be anyone here willing to put the time into it for free. Another solution would be to use a loop with an if statement. Something like this:

Please Login or Register  to view this content.

Note: There are a ton of ways to do this. The best way depends on a bunch of factors that are not included in your post

**kestefon** · 04-18-2013, 11:42 AM

Thanks. And yeah, I'll handle all of the heavy lifting--I'm just posting to get ideas and see if there's anything I haven't thought of.

By the way, question about the above: Since shifting would occur once you start deleting columns, wouldn't that icell = 5 potentially delete the wrong column?

**stnkynts** · 04-18-2013, 04:04 PM

That is a great question. I specifically set the macro to run from right to left (columns) so that column 5 will not change until after is has been passed. As long as you are only specifying 1 column not to delete then it should be fine. If you try to specify multiple columns then we would have to approach it differently which goes back to the "there are a ton of ways....just need more information"