Use of Range Property

[Guest]

I am trying to tighten up some old code that used a loop to change blank
cells to 0.
I want to use the following code but it doesn't work.

Range("A2").Select

Set MyRange = ActiveCell.CurrentRegion

MyRange.Select

Range(MyRange).SpecialCells(xlCellTypeBlanks) = "0" ' this does not work
' old code had a loop structure here using MyRange and it works but is slow

if I use [Range(a1:aq237).SpecialCells(xlCellTypeBlanks) = "0"] then it
works fine, any ideas.
thanx

 

[Bernie Deitrick]

Arnold,

Replace everything you have with

Range("A2").CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = "0"

or

Range("A2").CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = 0

HTH,
Bernie
MS Excel MVP

 

[Ron de Bruin]

Hi Arnold

Use it like this

Set MyRange = ActiveCell.CurrentRegion
MyRange.SpecialCells(xlCellTypeBlanks).Value = "0" '

 

[Guest]

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(xlCellTypeBlanks).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]

Arnold,

You need to put

Exit Sub

as the last line before your error handler, for cases when you don't have an error.

HTH,
Bernie
MS Excel MVP

 

[Guest]

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