VBA If Question

M

Matt

I'm using Ron de Bruin's "Delete row if a specific value exist
(VBA)". Can someone help me understand this section of the code:

'We check the values in the A column in this example
With .Cells(Lrow, "A")

If Not IsError(.Value) Then

If .Value = "ron" Then .EntireRow.Delete
'This will delete each row with the Value "ron"
'in Column A, case sensitive.

End If

End With

There are two IF statements but only one End If, why is that? I'm
trying to make it so that it will delete the row if one of multiple
values exist. When I use If-ElseIf, it skips all the ElseIfs and just
recognizes the first If. Can someone help?
 
S

smartin

Matt said:
I'm using Ron de Bruin's "Delete row if a specific value exist
(VBA)". Can someone help me understand this section of the code:

'We check the values in the A column in this example
With .Cells(Lrow, "A")

If Not IsError(.Value) Then

If .Value = "ron" Then .EntireRow.Delete
'This will delete each row with the Value "ron"
'in Column A, case sensitive.

End If

End With

There are two IF statements but only one End If, why is that? I'm
trying to make it so that it will delete the row if one of multiple
values exist. When I use If-ElseIf, it skips all the ElseIfs and just
recognizes the first If. Can someone help?
Click to expand...

The construction Ron gave boils down to

If foo Then
If bar Then x
End If

The inner "If" does not require an "End If" because the result is placed
on the same line of code.

This is convenient VBA shorthand for the equally valid

If foo Then
If bar Then
x
End If
End If

Which can be expanded to the likes of

If foo Then
If bar Then
x
ElseIf mom Then
y
Else
z
End If
End If

But, if /bar/, /mom/ and /dad/ should yield the same result, then this
will do

If foo Then
If bar Or mom Or dad Then
x
End If
End If

Or even

If foo Then
If bar Or mom Or dad Then x
End If
 
Top