score:4

Accepted answer

The reason is SpecialCells "looks" at the UsedRange when returning a range.

So using SpecialCells on an unused sheet will return Runtime Error '1004'. No Cells Found. (As an aside always use error handling with SpecialCells)

When you change cell A1, that becomes the sheets UsedRange, hence your Case 1 return of "A1"

The code below looks to return Range("A1:D4").SpecialCells(xlCellTypeBlanks) for

  1. A blank sheet - fails
  2. The sheet with A10 filled in - A1:A4
  3. The sheet with A10 cleared - A1:A4
  4. The sheet with the usedrange reset (which is effectively 1) - fails

    Sub Test()
    Dim ws As Worksheet
    Set ws = Sheets.Add
    On Error Resume Next
    'blank sheet
    Debug.Print "Blank sheet " & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address
    'enter in A10
    ws.[a10] = "test"
    Debug.Print "With A10 " & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address
    'clear a10
    ws.[a10].ClearContents
    Debug.Print "With A10 cleared " & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address
    'reset range
    ActiveSheet.UsedRange
    Debug.Print "With A10 cleared and range reset" & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address
    On Error GoTo 0
    End Sub
    

score:1

.SpecialCells work with the used range of a worksheet and not specific cells unless the specific cells fall within the used range.

To test Specialcells on an entirely new sheet will give you the error because the usedrange is just $A$1. If $A$1 is colored then you will not get an error as expected in CASE 2 above.

You have to use Specialcells with utmost care. Here is an example which will not give you an error on a new sheet.

Sub Sample()
    If ActiveSheet.UsedRange.Column > 1 Or ActiveSheet.UsedRange.Row > 1 Then
        Debug.Print ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Address
    End If
End Sub

Now type something in Cell D5. Say "Blah Blah"

Now run this code

Sub Sample()
    Debug.Print Sheet1.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address
End Sub

You will notice that this will work because the range is within the UsedRange Address.


Related Query

More Query from same tag