Keeping columns if header contains string - delete all others

[kalamazoo]

Hi all,

I have these 7 pieces of text that if the header contains them I want to keep them

SurveyCusAttr

SurveyIssueDateSSTZ

Location

QuestionName

QuesAnsKeyEntry

QuestID

QuesType

<tbody>
</tbody>

However the source I get them from has different text attached to them a lot of times that is always changing. For instance one quarter it would be 12345QuesType and the next quarter it would be 9871QuesType. Those pieces of text are the constants that I would be using as a unique identifier to keep the columns, and remove all other columns if they do not contain those unique identifiers.

Could anyone help set me on the correct path? I have worked with VBA before but this one seems a little over my head.

 

[Jambi46n2]

I have code that does something similar.. I'm not an expert but I believe the "*" with each header will act "If header is like"..

Hope this is a good start.

Sub KeepHeaders()

    Dim currentColumn As Integer
    Dim columnHeading As String


    For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1


        columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value


        'CHECK WHETHER TO KEEP THE COLUMN
        Select Case columnHeading



         Case "*SurveyCusAttr*", "*SurveyIssueDateSSTZ*", "*Location*", "*QuestionName*", "*QuesAnsKeyEntry*", "*QuestID*", "*QuesType*"

               'Do nothing
            Case Else
                    ActiveSheet.Columns(currentColumn).Delete
        End Select
    Next

End Sub

 

[kalamazoo]

Hi,

Thanks for the reply.

This actually ended up deleting everything. I thought it was maybe case sensitive so I changed that and it still seems to be deleting everything.

Anyone have any ideas? Thanks fort the help!

 

[John_w]

Like this (add the other headings):

        Select Case True
            Case columnHeading Like "*SurveyCusAttr*"
            Case columnHeading Like "*SurveyIssueDateSSTZ*"
            Case Else
                 ActiveSheet.Columns(currentColumn).Delete
        End Select

 

[kalamazoo]

Thanks John,

Pardon my newbiness but am I just supposed to

Sub Test()        

            Select Case True
            Case columnHeading Like "*SurveyCusAttr*"
            Case columnHeading Like "*SurveyIssueDateSSTZ*"
            Case Else
                 ActiveSheet.Columns(currentColumn).Delete
        End Select


End Sub

Giving me a 1004 error on the ActiveSheet.Columns(currentColumn).Delete line saying application defined or object-defined error. Apologies if I am misunderstanding you.

 

[John_w]

No. My code replaces the Select .... End Case block of code in post no. 2. You also need everything else above and below it.

 

[kalamazoo]

I thought that may be the case John. Thanks again for your help. Works like a charm.

So for any readers encountering a similar problem you have found the solution in this thread.