Delete all columns except the ones with specific header

T

Truiz

Active Member
Joined
Jul 14, 2014
Messages
339
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:

Code: 
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

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

Or

Code: 
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
ABCDEFGHIJ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]Don’t want[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]Don’t want[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]Don’t want[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep 3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]

</tbody>
Hoja1​




To

Excel 2012
ABCDEFG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep 3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: center"]I want to keep[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]

</tbody>
Hoja1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
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.
Click to expand...
If you use a method like this:
Code: 
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:
Code: 
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:
Code: 
If Cells(1,iCntr) <> "Whatever" Then
 
Upvote 0
By "Gap" I meant the issue of not being able to delete all except those I wish to keep by the header name

Joe4 said:
If you want to delete everything else and leave "Whatever", just change the IF line to:
Code: 
If Cells(1,iCntr) <> "Whatever" Then
Click to expand...


What If I have several columns I want to keep

Code: 
[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.
 
Last edited:
Upvote 0
You are using OR where you should be using AND, i.e.:
Code: 
[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
 
Upvote 0
Hey you were right!

Code: 
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

Code: 
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!
 
Upvote 0
You are welcome.

Glad you got things working the way you want.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Sending Email with Table in Body
Replies
3
Views
781
Domenic
D
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
128
sijpie
sijpie
D
  • Solved
VBA to paste table into Outlook
Replies
5
Views
822
Dani_LobP
D
J
  • Question Question
vba to find specific text in a column and then when found copy all the rows above it
Replies
1
Views
227
mumps
mumps
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
438
ChrisMac1
ChrisMac1

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top