Multiple Or Statement

mmertt900

New Member
Joined
Dec 18, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I'm having a truoble to solve my below problem. I need your advise.
I have a table like below first rows as header. And what I want to do is create a macro that helps me to find columns doesn't match my conditions and to be deleted. But my below code doesn't work. Aren't we allowed to use multiple Or statement ? Thanks in advance for your help.

A1B1C1
Müşteri Adı20Name

VBA Code:
Dim i as integer

For i = 1 To 30

    If Range("A1").Offset(0, i).Value <> "Müşteri Adı" Or "20" Or "40" Or "TR." Or "S. Liman" Or "T.Kilo" Then
    Columns(i + 1).Delete
    End If
Next i
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
125
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
try:

VBA Code:
If Range("A1").Offset(0, i).Value <> "Müşteri Adı" Or Range("A1").Offset(0, i).Value <>"20" Or Range("A1").Offset(0, i).Value <> "40" Or Range("A1").Offset(0, i).Value <>"TR." Or "S. Liman" OrRange("A1").Offset(0, i).Value <> "T.Kilo" Then

and you might go 30 to 1 step -1
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,484
Office Version
  1. 2019
Platform
  1. Windows
Hi,
when checking multiple values you may find it easier to place them in an array & check the cell value against it
Also, when deleting rows / columns in manner you are better to do it in reverse

Untested but see if this update to your code helps

VBA Code:
Sub DeleteColumns()
    Dim i       As Integer
    Dim arr     As Variant, m As Variant
    
    arr = Array("Müsteri Adi", 20, 40, "TR.", "S. Liman", "T.Kilo")
    
    For i = 30 To 1 Step -1
        m = Application.Match(Cells(1, i).Value, arr, 0)
        If IsError(m) Then Columns(i).Delete
    Next i
    
End Sub

Hope Helpful

Dave
 
Solution

mmertt900

New Member
Joined
Dec 18, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi,
when checking multiple values you may find it easier to place them in an array & check the cell value against it
Also, when deleting rows / columns in manner you are better to do it in reverse

Untested but see if this update to your code helps

VBA Code:
Sub DeleteColumns()
    Dim i       As Integer
    Dim arr     As Variant, m As Variant
   
    arr = Array("Müsteri Adi", 20, 40, "TR.", "S. Liman", "T.Kilo")
   
    For i = 30 To 1 Step -1
        m = Application.Match(Cells(1, i).Value, arr, 0)
        If IsError(m) Then Columns(i).Delete
    Next i
   
End Sub

Hope Helpful

Dave
Thank you very much. İt works perfecly. I've been trying improve my vba skills but array is hard to learn. I don't know why :)
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,484
Office Version
  1. 2019
Platform
  1. Windows
You are welcome and thanks for feedback we appreciate it

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,127,619
Messages
5,625,891
Members
416,141
Latest member
Bartek9q

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
Top