end sub when next visible cell is empty. Compile error: Block If Without End If

[zorcy3]

<code>I am using a filter to remove unwanted clutter. I then need to fill in column G and H, but only if there is a usable row. I am having troubles with row just below the filters. If the first visible row below the filters is blank, it needs to exit the sub.

Sub selectDirty()
Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$L$669").AutoFilter Field:=9, Criteria1:="=*Drty*" _
        , Operator:=xlAnd
    Dim myHRange As Range
   
    If Range("A2").CurrentRegion.SpecialCells(xlCellTypeVisible) = "" Then
    Exit Sub
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Name = "base"
    ActiveSheet.Range("base").Offset(0, 7).Select
    Selection.Value = "Failure"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Name = "base"
    
    ActiveSheet.Range("base").Offset(0, 6).Select
    Selection.Value = "Equipment"
End Sub

</code><code> </code>

 

[bodhi808]

I think you just need to add the line "End If" after "Exit Sub" like the following.

Sub selectDirty()
Rows("1:1").Select 
   Selection.AutoFilter 
   ActiveSheet.Range("$A$1:$L$669").AutoFilter Field:=9, Criteria1:="=*Drty*", Operator:=xlAnd 
   Dim myHRange As Range 
   If Range("A2").CurrentRegion.SpecialCells(xlCellTypeVisible) = "" Then 
   Exit Sub 
   End If 
   Range("A1").Select    
   Range(Selection, Selection.End(xlDown)).Name = "base" 
   ActiveSheet.Range("base").Offset(0, 7).Select    
   Selection.Value = "Failure" 
   Range("A1").Select    
   Range(Selection, Selection.End(xlDown)).Name = "base" 
   ActiveSheet.Range("base").Offset(0, 6).Select    
   Selection.Value = "Equipment"
End Sub

 

[zorcy3]

Thanks bodhi808. That did resolve the error. I had tried to add it in, but put it before the End Sub. That obviously failed. Do you see any reason this would not exit the sub if the first visible cell after A2 is blank?

 

[bodhi808]

I've not used the currentregion method really, but my understanding is that it represents an associated range to any data in your declared range.
Therefore, it is likely finding your entire list of data surrounding A2.
As this is a range of cells, you're unlikely to ever return a blank value in every cell, hence the IF statement never being true.

As I read your code, the range for the auto filter is hard coded as A1 to L669. Therefore, the next visible line after the autofilter is applied is always going to be row 670 if I understand correctly.
If this is the case, then perhaps replace the IF with:

If Application.CountA(Range("a670").EntireRow) = 0 Then
Exit Sub
End If