VBA ON ERROR does not work with SPECIALCELLS

[Felix]

I have a problem trapping errors using
"On Error goto ..." or "On Error Resume Next"

"On Error ..." works everywhere except when the offending
command uses SpecialCells as in
Selection.SpecialCells(xlCellTypeBlanks).Select

when not found, the macro errors with Run-time error 1004.

The same macros worked fine in xl2000 until I had to
install the hotfix from MS Support article 815406. Then
the problem started. Just upgraded to xl2003 but the
problem remains.

All the MS support articles refer only to protected sheets
which does not apply. Any ideas on a workaround?

 

[Fredrik Wahlgren]

I have a problem trapping errors using
"On Error goto ..." or "On Error Resume Next"

"On Error ..." works everywhere except when the offending
command uses SpecialCells as in
Selection.SpecialCells(xlCellTypeBlanks).Select

when not found, the macro errors with Run-time error 1004.

The same macros worked fine in xl2000 until I had to
install the hotfix from MS Support article 815406. Then
the problem started. Just upgraded to xl2003 but the
problem remains.

All the MS support articles refer only to protected sheets
which does not apply. Any ideas on a workaround?

I think you have to loop over the selection and make sure there's at least
one blank cell.

/Fredrik

 

[Harlan Grove]

Felix wrote...

I have a problem trapping errors using
"On Error goto ..." or "On Error Resume Next"

"On Error ..." works everywhere except when the offending
command uses SpecialCells as in
Selection.SpecialCells(xlCellTypeBlanks).Select

when not found, the macro errors with Run-time error 1004.

....

This *should* give an untrappable runtime error. When there are no
blank cells, .SpecialCells doesn't return a range object, so the
following .Select method call is invalid.

You need to use two steps to trap potential failure by .SpecialCells.


Dim r As Range

On Error Resume Next
Set r = Selection.SpecialCells(xlCellTypeBlanks)
Err.Clear
On Error GoTo 0

If r Is Nothing Then
MsgBox "No blank cells"
Else
r.Select
End If