IF w/Not Equal?

bobkap

Board Regular
Joined
Nov 22, 2009
Messages
192
I am trying to delete columns if the header of the column is NOT a header containing the word "Transaction Status" and other headers, but when I run this (below) it doesn't delete columns based on these criteria. It deletes every column.

Any help on what I'm doing wrong would be greatly appreciated.

If Cells(1, counter) <> "Transaction Status" Or Cells(1, counter) <> "Settlement Amount" Or Cells(1, counter) <> "Settlement Date/Time" Or Cells(1, counter) <> "Submit Date/Time" Or Cells(1, counter) <> "Date" Then
Columns(counter).Delete
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,776
Office Version
365, 2010
Platform
Windows, Mobile
If by the "containing" in "containing the word" you mean the word might not be the only text in the cell then you will also need to use another method, one way is something like...

Code:
Sub DelCols()
    Dim counter As Long
    For counter = 10 To 1 Step -1
        If InStr(Cells(1, counter), "Transaction Status") = 0 And _
          InStr(Cells(1, counter), "Settlement Amount") = 0 And _
            InStr(Cells(1, counter), "Settlement Date/Time") = 0 And _
              InStr(Cells(1, counter), "Submit Date/Time") = 0 And _
                InStr(Cells(1, counter), "Date") = 0 Then Columns(counter).Delete
    Next
End Sub
Although if this is the case you can do away with 2 of the tests as you are testing for "Date".
 
Last edited:

Forum statistics

Threads
1,086,116
Messages
5,387,927
Members
402,089
Latest member
Exceliamus

Some videos you may like

This Week's Hot Topics

Top