Compile Error: Block If without End If ??????

[APPPRO]

I've wrtitten the following macro to Clear certain Rows on a sheet if certain criteria are met on another sheet.
I keep getting a Compile Error: Block If without End If. The End Sub is highlighted. WHY? And how do I fix it?

Sub CLEAR_ROW()
'
' IF Sheets("ENTRY FORM").Range("k75") = 0 Then
Sheets("data").Rows("16:16").CLEAR
If Sheets("ENTRY FORM").Range("k74") = 0 Then
Sheets("data").Rows("15:15").CLEAR
If Sheets("ENTRY FORM").Range("k73") = 0 Then
Sheets("data").Rows("14:14").CLEAR
If Sheets("ENTRY FORM").Range("k72") = 0 Then
Sheets("data").Rows("13:13").CLEAR
If Sheets("ENTRY FORM").Range("k71") = 0 Then
Sheets("data").Rows("12:12").CLEAR
If Sheets("ENTRY FORM").Range("k70") = 0 Then
Sheets("data").Rows("11:11").CLEAR
If Sheets("ENTRY FORM").Range("k69") = 0 Then
Sheets("data").Rows("10:10").CLEAR
If Sheets("ENTRY FORM").Range("k68") = 0 Then
Sheets("data").Rows("9:9").CLEAR
If Sheets("ENTRY FORM").Range("k67") = 0 Then
Sheets("data").Rows("8:8").CLEAR
If Sheets("ENTRY FORM").Range("k66") = 0 Then
Sheets("data").Rows("7:7").CLEAR
End If
End Sub

 

[DonkeyOte]

If you do THEN and return you must have an End If - e.g

IF a=a then
a1 = a
End if

Or change your code

If A=a then a1=a

If the THEN and subsequent statement are on one line you do not need an End If.

 

[dk]

Each time you use an If statement that over more than one line, you must terminate it with an End If.

There are several ways you could change your code:-

First, terminate each individual If statement with an End If.

Sub CLEAR_ROW()
'
' IF Sheets("ENTRY FORM").Range("k75") = 0 Then
    Sheets("data").Rows("16:16").Clear
    If Sheets("ENTRY FORM").Range("k74") = 0 Then
        Sheets("data").Rows("15:15").Clear
    End If
    If Sheets("ENTRY FORM").Range("k73") = 0 Then
        Sheets("data").Rows("14:14").Clear
    End If
    If Sheets("ENTRY FORM").Range("k72") = 0 Then
        Sheets("data").Rows("13:13").Clear
    End If
    If Sheets("ENTRY FORM").Range("k71") = 0 Then
        Sheets("data").Rows("12:12").Clear
    End If
    If Sheets("ENTRY FORM").Range("k70") = 0 Then
        Sheets("data").Rows("11:11").Clear
    End If
    If Sheets("ENTRY FORM").Range("k69") = 0 Then
        Sheets("data").Rows("10:10").Clear
    End If
    If Sheets("ENTRY FORM").Range("k68") = 0 Then
        Sheets("data").Rows("9:9").Clear
    End If
    If Sheets("ENTRY FORM").Range("k67") = 0 Then
        Sheets("data").Rows("8:8").Clear
    End If
    If Sheets("ENTRY FORM").Range("k66") = 0 Then
        Sheets("data").Rows("7:7").Clear
    End If
End Sub

Alternatively, you could just put each If statement on one line:-

Sub CLEAR_ROW()
'
' IF Sheets("ENTRY FORM").Range("k75") = 0 Then
Sheets("data").Rows("16:16").Clear
If Sheets("ENTRY FORM").Range("k74") = 0 Then _
Sheets("data").Rows("15:15").Clear
If Sheets("ENTRY FORM").Range("k73") = 0 Then _
Sheets("data").Rows("14:14").Clear
If Sheets("ENTRY FORM").Range("k72") = 0 Then _
Sheets("data").Rows("13:13").Clear
If Sheets("ENTRY FORM").Range("k71") = 0 Then _
Sheets("data").Rows("12:12").Clear
If Sheets("ENTRY FORM").Range("k70") = 0 Then _
Sheets("data").Rows("11:11").Clear
If Sheets("ENTRY FORM").Range("k69") = 0 Then _
Sheets("data").Rows("10:10").Clear
If Sheets("ENTRY FORM").Range("k68") = 0 Then _
Sheets("data").Rows("9:9").Clear
If Sheets("ENTRY FORM").Range("k67") = 0 Then _
Sheets("data").Rows("8:8").Clear
If Sheets("ENTRY FORM").Range("k66") = 0 Then _
Sheets("data").Rows("7:7").Clear
End Sub

The _ character means that VBA treats those lines as one, but allows you to spread long lines over more than one row. In this case, you don't need an End If at all.

_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-10-21 09:21

 

[WJReid]

Hi APPPRO,
The reason for the error is that the If statements should be in one line each, no return after the THEN statement.You can also delete the End If statement before the End Sub

Regards,

Bill

 

[Andrew Poulsom]

Or another way of achieving your goal:

Sub Test()
    Dim c As Range
    Dim x As Integer
    For Each c In Sheets("ENTRY FORM").Range("K66:K75")
        If c.Value = 0 Then
            x = c.Row - 59
            Sheets("data").Rows(x).Clear
        End If
        Next c
End Sub

 

[APPPRO]

BILL:

That did it thanks.