Identifying column headings, keep and delete

[jscholds]

Hi all,

I would like to search and find column headings from a predefined list. When a column/s has been identified I would like to keep it/them and delete remaining ones.

Like..

Blue--Red--Orange--Green--Pink--Black--Yellow
1--------1------1--------1------1------1-------1
2--------2------2--------2------2------2-------2
3--------3------3--------3------3------3-------3
4--------4------4--------4------4------4-------4

In this example I want to keep columns called Red and Pink, but delete the rest. There is a catch though, the sheet won't have the same structure all the time i.e. in the example above Red is in column B and Pink in column E, this might change to A and M.

For the example above the macro would give me a sheet with...

Red--Pink
1-------1
2-------2
3-------3
4-------4

I know the names of the column headings I would like to find and I don't mind copying columns to a new sheet if that is that is necessary.

At the moment I program the macro individually to fit the structure of the excel sheet I am currently working using, however it takes time there and manual delete increases chance for errors.

Running W2000 on excel 2000.

Hope someone can help..

Jakob

[Davers]

Hi Jakob,

Try something like this...

Sub dltCol()

LastCol = ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = False

For r = LastCol To 1 Step -1
    If Cells(1, r).Value <> "RED" And Cells(1, r).Value <> "PINK" Then Columns(r).Delete
Next r

End Sub

This is assuming the headers are in the first row...

Hope this helps and have a good day,

Dave

[onlyadrafter]

Hello,

Is the predefined list on the spreadsheet somewhere, and if so where, if not is it OK just to put straight into the macro?

Just noticed Davers reply, which, if the answer to my second question is Yes, has answered this problem.

[jscholds]

Thank you Davers and onlyadrafter - excellent and yes - it's fine being defined straight in the macro!

Where do you learn all this guys.. very impressed!

Jakob

[Davers]

Heh, speaking only for myself...80% of my stuff comes from bits and pieces found on this board...the best board in the world!!!! 19% comes from a stack of Excel books crowding my desk, and 1% comes from my own noodle...

Have a good day,

Dave

[onlyadrafter]

Hello

Much the same as Davers, learnt most it by having to use it for work. So a quick learning curve there.

I thought I had learnt a lot, then I discovered this forum, and boy, was I in for a shock, my knowledge was minimal. Have learnt an amazing amount of stuff here, complex formulae, effective and simple VB. There are too many people to mention whose efforts here have helped my knowledge, thus enabling me to help others.

In time you will catch up with others and pass on your knowldege. Just keep plugging away. As with anything, things are only easy if you know the answer.