Check range for Null or 0 value

[USAMax]

Is there a way to check a range for null or zero value.

I need to update large ranges but only if there is no data or if the values are zero. I was thinking of something like:

If IsNull(Range(Cells(1,1), Cells(10,10))

or

If Sum(Range(Cells(1,1), Cells(10,10))) = 0

Any thoughts?

 

[jai9]

Hi,

Try,

For Each rcell in rrange
If rcell.value = "" OR rcell.value= 0 Then
"Do this"
Next rcell

Jai

 

[USAMax]

My formula is very simplified but I am looking at thousands of cells that I need to add a formula to if the range is empty.

Range(Cells(1, 1),Cells(100, 239).Formula = "=P5!R[-72]C[39]"

Updating a single cell at a time takes too long so I need to know if the range is empty or zero.

 

[jai9]

Hi,

I do not think you can check and update the complete range at a time. You have to do it cell by cell.

But I may be wrong.

Maybe someone who knows more about it can help.

Jai

 

[USAMax]

I can update a whole range using the formula I listed above but in this case I only want to update cells that are Zero or Empty.

How do I select or define the rrange for the formula you listed?

 

[jai9]

Hi,

Dim rrange as Range
Dim rcell as Range

Set rrange = Range("A1:D1000")

Jai