Help with multiple criteria

[Dazzawm]

I am learning code so please look at the code below. It looks for "Front Link Rod" then deletes the row. What if I want to look for other things as well like "Rear Link Rod", do I put a comma like in red, Well I know thats not right because it doesnt work! How do I write it please?

Sub test()
Dim a As Integer
Dim b As Integer
Range("S1").Select
a = ActiveCell.CurrentRegion.Rows.Count
For b = 1 To a
If Selection.Value = "Front Link Rod, Rear Link Rod" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Next b
End Sub

 

[rudfaden]

Try to use AND

if Selection.Value = "Front Link Rod" and Selection.Value ="Rear Link Rod" Then

 

[Dazzawm]

That doesnt work, it deletes neither.

 

[Dazzawm]

I tried it with OR and that seemed to work.

 

[hiker95]

dazwm,

When you are inserting or deleting rows is always better to loop from the last row to the first row.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub test()
Dim a As Long
Dim b As Long
Range("S1").Select
a = ActiveCell.CurrentRegion.Rows.Count
For b = a To 1 Step -1
  If Selection.Value = "Front Link Rod" Or Selection.Value = "Rear Link Rod" Then
    Selection.EntireRow.Delete
  Else
    Selection.Offset(-1, 0).Select
  End If
Next b
End Sub

 

[Dazzawm]

dazwm,

When you are inserting or deleting rows is always better to loop from the last row to the first row.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub test()
Dim a As Long
Dim b As Long
Range("S1").Select
a = ActiveCell.CurrentRegion.Rows.Count
For b = a To 1 Step -1
  If Selection.Value = "Front Link Rod, Rear Link Rod" Then
    Selection.EntireRow.Delete
  Else
    Selection.Offset(-1, 0).Select
  End If
Next b
End Sub

Would you mind explaining what is different in the code please, especially the step -1 part. Thanks

 

[hiker95]

dazwm,

When you are inserting or deleting rows it is always better to loop from the last row to the first row.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub test()
Dim a As Long
Dim b As Long
Range("S1").Select
a = ActiveCell.CurrentRegion.Rows.Count
For b = a To 1 Step -1
  If Selection.Value = "Front Link Rod" Or Selection.Value = "Rear Link Rod" Then
    Selection.EntireRow.Delete
  Else
    Selection.Offset(-1, 0).Select
  End If
Next b
End Sub

 

[Dazzawm]

You have just copied the previous post. I dont understand how in the code it is starting at the bottom.

 

[hiker95]

dazwm,

Sorry, I am getting ahead of myselff.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub test()
Dim a As Long
Dim b As Long
Range("S1").Select
a = ActiveCell.CurrentRegion.Rows.Count
For b = 1 To a
  If Selection.Value = "Front Link Rod" Or Selection.Value = "Rear Link Rod" Then
    Selection.EntireRow.Delete
  Else
    Selection.Offset(1, 0).Select
  End If
Next b
End Sub

 

[Dazzawm]

I tried it with OR and that seemed to work.

Now you are confusing me! So what I said in this post is correct?