Use of Range Property
 

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

Use of Range Property
 

Arnold,

Replace everything you have with

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

or

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

HTH,
Bernie
MS Excel MVP


"Arnold Klapheck" wrote in message
...
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

Use of Range Property
 

Hi Arnold

Use it like this

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Arnold Klapheck" wrote in message
...
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

Use of Range Property
 

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

Use of Range Property
 

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


"Arnold Klapheck" wrote in message
...
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

Use of Range Property
 

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