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

 

[VoG]

This is more compact and more efficient. With very few exceptions it is not necessary to select and selecting slows the code. You have to loop backwards when deleting

Sub test2()
Dim LR As Long, i As Long
LR = Range("S" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("S" & i).Value = "Front Link Rod" Or Range("S" & i).Value = "Rear Link Rod" Then Rows(i).Delete
Next i
End Sub

 

[hiker95]

dazwm,

The only line of code in your original post that should be changed is this line:

  If Selection.Value = "Front Link Rod, Rear Link Rod" Then

Change to this:

  If Selection.Value = "Front Link Rod" Or Selection.Value = "Rear Link Rod" Then

 

[VoG]

dazwm,

The only line of code in your original post that should be changed is this line:

  If Selection.Value = "Front Link Rod, Rear Link Rod" Then

Change to this:

  If Selection.Value = "Front Link Rod" Or Selection.Value = "Rear Link Rod" Then

Sorry, no. You have to go backwards.

 

[Dazzawm]

This is more compact and more efficient. With very few exceptions it is not necessary to select and selecting slows the code. You have to loop backwards when deleting

Sub test2()
Dim LR As Long, i As Long
LR = Range("S" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("S" & i).Value = "Front Link Rod" Or Range("S" & i).Value = "Rear Link Rod" Then Rows(i).Delete
Next i
End Sub

So to clarify your 'LR' is the same as my 'a', and your 'i' is the same as my 'b'. And because the range will start with the first row the 'step -1' will take it to the bottom?

 

[VoG]

My LR is the last filled row.

For i = LR To 1 Step -1

Loops backwards from the last filled row to row 1 in steps of 1 row.

 

[Dazzawm]

So

LR = Range("S" & Rows.Count).End(xlUp).Row

Counts the rows from the bottom upwards and my code

a = ActiveCell.CurrentRegion.Rows.Count

Counts from the top downwards?

 

[VoG]

Yes, correct. The big difference is that yours relies on a cell in column S being selected whereas mine does not.

 

[Dazzawm]

Yes, correct. The big difference is that yours relies on a cell in column S being selected whereas mine does not.

Sorry if I sound dim but I dont understand

 

[VoG]

Here you have to select a cell to start with

Range("S1").Select
a = ActiveCell.CurrentRegion.Rows.Count

Selecting is unnecessary and inefficient.

LR = Range("S" & Rows.Count).End(xlUp).Row

requires only that the sheet that you wish to operate on is selected.

 

[Dazzawm]

So should I use your method always or just this particular type of code?