Syntax error in code

[Guest]

I'm using this code in an existing macro to keep only rows with "F800" in
column J. All other rows are being deleted. The spreadsheet varies in
length every day so there is no end specified. The syntax error I'm
receiving in my code is on this line:

If StrComp(Cells(RowNdx, "J"), "F800", vbTextCompare) <> 0
Then



Thanks for your help.

With Selection
Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If StrComp(Cells(RowNdx, "J"), "F800", vbTextCompare) <> 0
Then
Rows(RowNdx).Delete
End If
Next RowNdx
End With

 

[Dave Peterson]

First, you use "with selection", but don't use anything that belongs to that
selection--I'm not sure what you wanted to do with that line.

Second, "Then" belongs on the same logical line as the "If" statement.

Option Explicit
Sub testme01()
Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If StrComp(Cells(RowNdx, "J"), "F800", vbTextCompare) <> 0 Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

If the "if" portion gets too long, you can use a line continuation character to
continue that "logical" line to the next "physical" line:

Option Explicit
Sub testme01()
Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If StrComp(Cells(RowNdx, "J"), "F800", vbTextCompare) <> 0 _
Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

The space character followed by the underscore is that continuation character.

=======
If you're coming from an environment where you used to line up the If/Then/else,
you will get used to VBA's syntax.

if a = b
then do something
do something else
else
do something2
do something3.

becomes

if a = b then
do something
do something else
else
do something2
do something3
end if