Excel 2007 : Macro to keep columns by specific column heading names

**Tracy Ross** · 03-08-2012, 06:36 PM

I pull an excel report frequently that has approx 200 columns - I only need about 100 of them. How do I create a macro to keep only the columns I need by specific column heading name?

**TMS** · 03-08-2012, 06:59 PM

What is/are the criteria for hiding the columns you don't want/need?

Regards, TMS

**Tracy Ross** · 03-08-2012, 08:09 PM

I simply do not need/use them.

**abousetta** · 03-08-2012, 08:39 PM

What TMS means is how can we write a macro without knowing which ones are supposed to be hidden. Please upload a copy of your workbook and you can dummy up the data but the structure should remain the same. Also we need to know which columns you want to keep or which you want to hide.

**Tracy Ross** · 03-09-2012, 12:50 PM

This was solved for me by WINKO - THANKS!!:

Sub Keep_specific_columns1()
'
' DeleteColumnsOnSheet Macro
' Developer: Winko Erades van den Berg
' E-mail : winko at winko-erades.nl
' Developed: 09-03-2012
' Modified: 09-03-2012
' Version: 1.0
'

Dim iCol As Long

'Constant values
data_sheet1 = InputBox("TEST1:") 'Create Input Box to ask the user which sheet needs to be reorganised

'Start with counting columns
For iCol = 1 To Sheets(data_sheet1).UsedRange.Columns.Count

'Read the header of the sheet to determine whether the column needs to be deleted
TargetCol = 0
If Sheets(data_sheet1).Cells(1, iCol).Value = "L72A" Then TargetCol = 1
If Sheets(data_sheet1).Cells(1, iCol).Value = "L73A" Then TargetCol = 1
If Sheets(data_sheet1).Cells(1, iCol).Value = "L74A" Then TargetCol = 1

'If a column doesn't match then delete column
If TargetCol = 0 Then
Sheets(data_sheet1).Columns(iCol).Delete
If iCol < Sheets(data_sheet1).UsedRange.Columns.Count Then
iCol = iCol - 1
Else
End If
End If

Next iCol 'Move to the next column until all columns are read

End Sub

**TMS** · 03-09-2012, 02:17 PM

I'm glad you have a solution and you have shared it with us. He clearly has some infomation or insight that we do not have.

As Winko appears not to be a member of this forum, I suspect that you have cross posted without making us aware of that fact.

That implies that your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

And ...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Regards, TMS

**Tracy Ross** · 03-16-2012, 12:44 PM

I did not cross-post. I found WINKO on this Forum. ??

**abousetta** · 03-16-2012, 12:55 PM

TMS means that any communications regarding possible solutions should be made publicly in this thread. If WINKO provided you the solution via a private channel then this is against the forum rules. Since there are no posts in this thread from anyone named WINKO therefore the natural assumption is that the OP cross-posted (which happens more often that you can imagine). It's frustrating to spend time working out a solution to only realize that the answer was already provided by someone else on another forum. That drains the energy and time that we have to help people. I hope you understand and will clarify the sources of your solutions in the future.

Good luck.

abousetta

P.S. If you have found an adequate solution then please mark the thread as solved and add code tags around your posted code. Thanks.

**TMS** · 03-16-2012, 01:38 PM

No WINKO.

Maybe one of these ....

winiko wink Wink121 winkblink winklar winkle winkle10ca winklenugget Winkler1 winklerg winklew winkus

Whatever, there's no input from him in the thread which rather defeats the purpose of an open forum.

Regards, TMS