SpecialCells method of Excel Range strange behaviour

Question

I have written a macro to find blank cells from a certain range using SpecialCells method of excel range object. When i am trying to execute following code i get a exception as "No cells found".

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

Steps:-

1. Open a new Excel instance.
2. Press ALT + F11/ Open VBE
3. Insert a new Module
4. Paste the above code and run test macro as specified above.

Output Received:

Runtime Error '1004'. No Cells Found.

Output Expected:

$A$1:$D$4

Case 1:

Now if i do any operation of cell A1. Operation such as giving fill color, etc. Call the test() macro then it does not throw exception.

Output Received:

$A$1

Output Expected:

$A$1:$D$4

Case 2:

Suppose if i give any value in cell B3. Call the test() macro, exception is not thrown.

Output Received:

$B$1:$B$2,$A$1:$A$3

Output Expected:

$A$1:$D$4

Case 3:

If i try to edit value or fill the cell outside the range "A1:D4" for example E10 and execute test() method then it gives me the proper output.

Output Received:

$A$1:$D$4

Note: Execute each Case with a new excel instance.

Answer 1

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
   

Answer 2

.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.