Problem counting blank cells

[307Fergie]

The code below is part of a larger macro which, subject to certain conditions, creates a blank cell in column A. This is used as a marker to enable the entire row to be deleted later. In testing I have found that very occasionally a file will come through which does not meet the conditions, and so no blank cell is created. At this point the code falls over with an error that the cells to be deleted cannot be found. In order to overcome this I wrote the posted code. I have used this successfully on numerous occasions, but always to count an actual value as opposed to a blank cell. I am still getting the same error message as the If statement doesn't appear to be working and I think this is because it's not counting the blank cells. I just can't work out what I am doing wrong. Any help would be appreciated.

Dim instances1 As Long
        instances1 = WorksheetFunction.CountIf(Range("A:A"), "")
            If instances1 > 0 Then _
                Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete              
           End If

 

[Joe4]

You can bypass/ignore those errors like this:

        [COLOR=#ff0000]On Error Resume Next[/COLOR]
        Dim instances1 As Long
        instances1 = WorksheetFunction.CountIf(Range("A:A"), "")
        If instances1 > 0 Then _
            Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete              
        End If
        [COLOR=#ff0000]On Error GoTo 0[/COLOR]