Delete Macro with OR/ELSE condition

[Mike]

I am a beginner a writing Macros in Excel. I have a Macro that deletes any
record that does not equal "Server/Midrange Software" in column M. I need to
expand this to include other criteria. Such as,

if column M does not equal "Server/Midrange Software" or does not equal
"Data Telecom" then delete the record.

The Macro I have currently is.....

Sub TryMe()
'Deletes rows where the value in column M are not equal to Server/Midrange
Software
Dim RowMdx As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "M").End(xlUp).Row

For RowMdx = LastRow To 1 Step -1
If Cells(RowMdx, "M").Value <> "Server/Midrange Software" Then
Rows(RowMdx).Delete

End If
Next RowMdx
End Sub

How do I put the OR condition in this for Data Telecom and possibly more
conditions?

Any help appreciated,,,,,,thanks

 

[Dave Peterson]

If the number of options is small, you can use something like:

if lcase(Cells(RowMdx, "M").Value) = lcase("Server/Midrange Software") _
or lcase(Cells(RowMdx, "M").Value) = lcase("Data Telecom") _
or lcase(Cells(RowMdx, "M").Value) = lcase("some other value") _
'keep it
else
rows(rowmdx).delete
end if

When the number of entries gets bigger, you may want to use a different
approach...

Dim res as variant
dim myList as variant

mylist = array("Server/Midrange Software", _
"Data Telecom", _
"some other value")

....

for RowMdx = LastRow To 1 Step -1
res = application.match(cells(rowmdx,"M").value, mylist,0)

if isnumber(res) then
'there was a match, skip it
else
Rows(RowMdx).Delete
End If
Next RowMdx

The worksheet function =match() isn't case sensitive.

 

[Mike]

I tried this below but it deletes every record.

Sub TryMe()
'Deletes rows where the value in column M is not equal to Server/Midrange
Software
Dim RowMdx As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "M").End(xlUp).Row

For RowMdx = LastRow To 1 Step -1
If LCase(Cells(RowMdx, "M").Value) <> LCase("Server/Midrange Software") _
Or LCase(Cells(RowMdx, "M").Value) <> LCase("Data Telecom") Then
Rows(RowMdx).Delete
End If

Next RowMdx
End Sub

When I use this it deletes only Server/Midrange Software

Sub TryMe()
'Deletes rows where the value in column M is not equal to Server/Midrange
Software
Dim RowMdx As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "M").End(xlUp).Row

For RowMdx = LastRow To 1 Step -1
If Cells(RowMdx, "M").Value <> "Server/Midrange Software" Then
Rows(RowMdx).Delete
End If

Next RowMdx

End Sub


So I need obviously I am doing something wrong with the first Macro..

Any Help?

 

[Dave Peterson]

Change the OR to AND.

Or use OR and Else like I did.

I tried this below but it deletes every record.

Sub TryMe()
'Deletes rows where the value in column M is not equal to Server/Midrange
Software
Dim RowMdx As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "M").End(xlUp).Row

For RowMdx = LastRow To 1 Step -1
If LCase(Cells(RowMdx, "M").Value) <> LCase("Server/Midrange Software") _
Or LCase(Cells(RowMdx, "M").Value) <> LCase("Data Telecom") Then
Rows(RowMdx).Delete
End If

Next RowMdx
End Sub

When I use this it deletes only Server/Midrange Software

Sub TryMe()
'Deletes rows where the value in column M is not equal to Server/Midrange
Software
Dim RowMdx As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "M").End(xlUp).Row

For RowMdx = LastRow To 1 Step -1
If Cells(RowMdx, "M").Value <> "Server/Midrange Software" Then
Rows(RowMdx).Delete
End If

Next RowMdx

End Sub

So I need obviously I am doing something wrong with the first Macro..

Any Help?

 

[Dave Peterson]

Ps. Notice that I used OR and ELSE and a comparison operator of =.

I find that easier to understand.

 

[Mike]

Dave, thanks a bunch, the OR worked great...I should have know that from my
old COBOL days but didn't think of it.....

Thanks again