Delete all columns except the ones with specific header

[Truiz]

Hello All,

I was wondering how could I delete all the columns I do not need from a report without having to either mention them or have their address?

Usually when I want to clean up a report I delete columns using something like:

Range("B:B,D:D,F:G,I:I,K:K,M:N,P:S,U:BH,BJ:BJ,BL:BX").Delete Shift:=xlToLeft

Or something like this

For iCntr = lCol To 1 Step -1    If Cells(1,iCntr) = "Whatever" Then
    Columns(iCntr).Delete
    End If
    Next

Or

For iCntr = lCol To 1 Step -1  Select Case Cells(1, lCol)
    Case "Header1", "Header2", "", "Unnamed Header"
      Columns(iCntr).Delete
  End Select
Next

But I noticed using any of this methods leaves me with a gap, what happens if the order of the columns change and no longer can use a range, what if they add another column or columns????? None of this will delete the new ones.

So I was wondering how would I go about deleting all the columns except those Whose headers I want to keep i.e

go from

Excel 2012

	A	B	C	D	E	F	G	H	I	J
1	I want to keep	I want to keep 1	Don’t want	I want to keep	Don’t want	I want to keep 2	I want to keep	Don’t want	I want to keep 3	I want to keep
2	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
3	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
4	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
5	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Hoja1​

To

Excel 2012

	A	B	C	D	E	F	G
1	I want to keep	I want to keep 1	I want to keep	I want to keep 2	I want to keep	I want to keep 3	I want to keep
2	Data	Data	Data	Data	Data	Data	Data
3	Data	Data	Data	Data	Data	Data	Data
4	Data	Data	Data	Data	Data	Data	Data
5	Data	Data	Data	Data	Data	Data	Data

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Hoja1​

<tbody>
</tbody>

 

[Joe4]

I am not sure I understand what you mean by this:

But I noticed using any of this methods leaves me with a gap, what happens if the order of the columns change and no longer can use a range, what if they add another column or columns????? None of this will delete the new ones.

If you use a method like this:

For iCntr = lCol To 1 Step -1    
    If Cells(1,iCntr) = "Whatever" Then
        Columns(iCntr).Delete
    End If
Next

if lCol is dynamically calculated just before the loop like this:

lCol = Cells(1, Columns.Count).End(xlToLeft).Column

it doesn't matter how many columns you may have or what order they are in.

And it doesn't leave "a gap", it shifts all the columns over the to left to fill in the column that was just deleted.

If you want to delete everything else and leave "Whatever", just change the IF line to:

If Cells(1,iCntr) <> "Whatever" Then

 

[Truiz]

By "Gap" I meant the issue of not being able to delete all except those I wish to keep by the header name

If you want to delete everything else and leave "Whatever", just change the IF line to:

If Cells(1,iCntr) <> "Whatever" Then

What If I have several columns I want to keep

[COLOR=#333333]If Cells(1,iCntr) <> "Whatever" OR "Whatever1" OR "Whatever2" Then[/COLOR]

Does that work? I'm not sure how the OR statement works on VBA I have read some documentation but have never gotten it to work.

 

[Joe4]

You are using OR where you should be using AND, i.e.:

[COLOR=#333333]If (Cells(1,iCntr) <> "Whatever") AND [/COLOR](Cells(1,iCntr) <> "Whatever2") AND (Cells(1,iCntr) <> "Whatever3") Then
    Columns(iCntr).Delete
End If

Think about logically, and it might make more sense.
What you are saying is the following: only delete the column if ALL those criteria are true (it cannot be any of those names)
If we used OR, only one would have to be True in order for it to delete the column, meaning ALL columns would end up being deleted (as any one column cannot have two different titles at once!).

If you have a lot of names of columns that you want to keep, you might want to have a list somewhere (a list or an array), and do some sort of function to see if the name of the column is in the list anywhere. Like was done here: https://stackoverflow.com/questions/38267950/check-if-a-value-is-in-an-array-or-not-with-excel-vba

 

[Truiz]

Hey you were right!

Sub test()Dim lcol As Long
Dim vtfc As Long


        
        lcol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
               SearchDirection:=xlPrevious, LookIn:=xlValues).Column
    
  For vtfc = lcol To 1 Step -1
        If (Cells(1, vtfc) <> "I want to keep") And (Cells(1, vtfc) <> "I want to keep 2") Then
        Columns(vtfc).Delete
        End If
  Next
    
End Sub

Works awesome!


I read a couple of question in the suggested site and also found an alternative

Sub test()Dim lcol As Long
Dim vtfc As Long


        
        lcol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
               SearchDirection:=xlPrevious, LookIn:=xlValues).Column
    
  For vtfc = lcol To 1 Step -1
        Select Case Cells(1, vtfc)
        Case "I want to keep", "I want to keep 2"
        'Will do nothing if the value is matched
        Case Else
        Columns(vtfc).Delete
        End Select
  Next
    
End Sub

I will read a bit deeper to be able as you suggested to be able to create a list or array to pass through Thanks!

 

[Joe4]

You are welcome.

Glad you got things working the way you want.