SCREENSHOT INCL. - Fix Macro to Delete Columns whose header is not either "A" or "B"

[klopera1]

I have searched thoroughly the forums on how to keep only columns whose header is the text "x","y" or "c".. and saw a common formula pasted below:


Option Explicit
Option Compare Text

Public Sub DeleteColumns()

Dim iLastCol As Long
Dim iPtr As Long

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

For iPtr = iLastCol To 1 Step -1
If InStr(Cells(1, iPtr).Value, "Review ID") > 0 Then
Columns(iPtr).Delete Shift:=xlToLeft

End If
Next iPtr

End Sub


However, whne I paste this directly into my VBA (with adjusted "iptr" and text name), I go through each line with F8 and it just keeps going back an forth between the beggining of the IF statement,and the "End if", while skipping the Columns.Delete command.This is a screenshot of a simplified version of what I'd need to do. I want the macro to delete every column that is not exactly "Review ID" or "Issue ID" (will include about 15 columns headsreas that I need to keep out of 50 or so columns)[TABLE="width: 514"]
<TBODY>[TR]
[TD="class: xl33875, width: 108, bgcolor: #002d72"]Review ID
[/TD]
[TD="class: xl33875, width: 121, bgcolor: #002d72"]Audit ID
[/TD]
[TD="class: xl33875, width: 110, bgcolor: #002d72"]Issue ID
[/TD]
[TD="class: xl33875, width: 164, bgcolor: #002d72"]Legal Entity
[/TD]
[TD="class: xl33875, width: 181, bgcolor: #002d72"]IA Division Owner
[/TD]
[/TR]
[TR]
[TD="class: xl33876, bgcolor: white"][/TD]
[TD="class: xl33876, bgcolor: white"]0
[/TD]
[TD="class: xl33876, bgcolor: white"]R13349-01
[/TD]
[TD="class: xl33876, bgcolor: white"].
[/TD]
[TD="class: xl33876, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl33876, bgcolor: white"][/TD]
[TD="class: xl33876, bgcolor: white"]0
[/TD]
[TD="class: xl33876, bgcolor: white"]R13720-01
[/TD]
[TD="class: xl33876, bgcolor: white"].
[/TD]
[TD="class: xl33876, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl33876, bgcolor: white"][/TD]
[TD="class: xl33876, bgcolor: white"]0
[/TD]
[TD="class: xl33876, bgcolor: white"]R14039-01
[/TD]
[TD="class: xl33876, bgcolor: white"][/TD]
[TD="class: xl33876, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl33876, bgcolor: white"][/TD]
[TD="class: xl33876, bgcolor: white"]0
[/TD]
[TD="class: xl33876, bgcolor: white"]R14039-02
[/TD]
[TD="class: xl33876, bgcolor: white"][/TD]
[TD="class: xl33876, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl33876, bgcolor: white"][/TD]
[TD="class: xl33876, bgcolor: white"]0
[/TD]
[TD="class: xl33876, bgcolor: white"]R14040-02
[/TD]
[TD="class: xl33876, bgcolor: white"][/TD]
[TD="class: xl33876, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl33876, bgcolor: white"][/TD]
[TD="class: xl33876, bgcolor: white"]0
[/TD]
[TD="class: xl33876, bgcolor: white"]R14040-03
[/TD]
[TD="class: xl33876, bgcolor: white"][/TD]
[TD="class: xl33876, bgcolor: white"][/TD]
[/TR]
</TBODY>[/TABLE]
Thank You!




</PRE>

 

[Jonmo1]

The code works fine for me.

Check for exact spelling and extra spaces in your column headers. "Review ID" vs " Review ID" vs "Review ID "

 

[AlphaFrog]

I have to ask the obvious; are the headers are in row 1?

 

[Jonmo1]

Althogh I just noticed it actually deletes the columns that DO = Review ID
But you want the opposite, right?
Delete the columns that do NOT = Review ID..

Change
If InStr(Cells(1, iPtr).Value, "Review ID") > 0 Then
to
If InStr(Cells(1, iPtr).Value, "Review ID") = 0 Then

 

[Jonmo1]

If you ultimately have 15 headers to exclude from being deleted, try it like this.

Option Explicit
Option Compare Text
Public Sub DeleteColumns()
Dim iLastCol As Long
Dim iPtr As Long
Dim MyHeaders As Variant, xMatch As Variant

'Add the desired headers to this list
MyHeaders = Array("Review ID", "Issue ID")

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

For iPtr = iLastCol To 1 Step -1
    xMatch = Application.Match(Cells(1, iPtr).Value, MyHeaders, 0)
    If IsError(xMatch) Then
        Columns(iPtr).Delete Shift:=xlToLeft
    End If
Next iPtr
End Sub

 

[klopera1]

Jonmo1, thank you for reply!

nice point - I did not catch it that- my proble was fixed by just running the whole macro rather than F8'ing it,

This was my first using Mr.Excel in my efforts to learn Macros.. Than you for quick response!!!

 

[klopera1]

AlphaFrog, yes they are in row 1, Thank you for reply! problem seems fixed now that I jsut ran the macro in its entirety.

This was my first using Mr.Excel in my efforts to learn Macros.. Than you for quick response!!!!

 

[klopera1]

Jonno1, thank you for this code, which has been helping me run my macro these past couple of days,
I have a follow-up on this regarding how to alter code if the headers change from row 1 to another row... here is the link to the question - http://www.mrexcel.com/forum/excel-...hen-header-not-row-1-chart-acro-included.html

Thank You!

 

[MARK858]

See your other post.