specialcells raises an error if there are no blank cells in the range

Use this for illustration

Sub FillZeros()
Dim rng as Range, rng1 as Range
set rng = Range("A2").currentRegion
if rng.count = 1 then
if isempty(rng) then
rng.value = 0
end if
else
on Error Resume Next
set rng1 = rng.SpecialCells(xlBlanks)
On error goto 0
if rng1 is nothing then
msgbox "No blank cells in " & rng.address
else
rng1.Value = 0
end if
end if
End sub

--
Regards,
Tom Ogilvy


"Arnold Klapheck" wrote:

This seems to be working (changing blanks to 0) but it is going into my
Errorhandler and returning 0: any idea why it is
returning an error? should I just take the errorhandler out?

Sub Zero_Fill_In()
On Error GoTo Errorhandler
Application.ScreenUpdating = False
Range("A2").CurrentRegion.SpecialCells(xlCellTypeB lanks).Value = 0
' also tried it with "0", does same thing
Errorhandler:
Dim Msg As String
Msg = Err.Number & ":" & Err.Description
MsgBox Msg

End Sub

"Bernie Deitrick" wrote:

Arnold,

Replace everything you have with

Range("A2").CurrentRegion.SpecialCells(xlCellTypeB lanks).Value = "0"

or

Range("A2").CurrentRegion.SpecialCells(xlCellTypeB lanks).Value = 0