Check if all cells in range are empty?

[Cymae]

I have an if statement as follows:

If IsEmpty(Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol))) Then

i did a select to make sure it was selecting the whole range I want and it works fine:

Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol)).Select

Inside my range I can have cells with 0s in them and cells with nothing in them. What I would like my if statement to do is return true ONLY when ALL cells have nothing in them. At the moment, even if I have 0's in some cells, it's returning false.

Am I doing something wrong?

 

[Cymae]

So, we are back to doing a sum function on a range within a row. If the range I am looking at is empty or any cell in the range is not a number, I want to skip it. The empty part works fine, now I just have to get it to ignore ranges that include non-numeric cells.

 

[Yogi Anand]

Hi Cymae:

You may want to try ...

If myRange.Cells.Count = Evaluate("=sumproduct((isblank(" & myRange.Address & ")+0))") Then
    MsgBox "all cells are empty"
    Else
    MsgBox "not all cells are empty"
End If

Let me know if this works for you.

 

[Cymae]

Hi Cymae:

You may want to try ...

If myRange.Cells.Count = Evaluate("=sumproduct((isblank(" & myRange.Address & ")+0))") Then
    MsgBox "all cells are empty"
    Else
    MsgBox "not all cells are empty"
End If

Let me know if this works for you.

I have the empty part working now thanks to jindon's excellent advice, I am trying to get the numeric part going

 

[jindon]

So, we are back to doing a sum function on a range within a row. If the range I am looking at is empty or any cell in the range is not a number, I want to skip it. The empty part works fine, now I just have to get it to ignore ranges that include non-numeric cells.

And how hidden cells are concerned ?

 

[Cymae]

And how hidden cells are concerned ?

Um..they aren't really. I thought that was a possible cause why the other formula you gave me wasn't working, and I ruled it out when I got it to work

 

[jindon]

So, we are back to doing a sum function on a range within a row. If the range I am looking at is empty or any cell in the range is not a number

Then simplh use Count function

Application.Count(Range) <> Range.Cells.Count Then
blank or cells other than numeric value exists

 

[Cymae]

Then simplh use Count function

Application.Count(Range) <> Range.Cells.Count Then
blank or cells other than numeric value exists

Much Obliged. But will this return true even If I have some blank cells?

Because SOME Blank cells are OK, I just don't want non-numeric cells or ALL blank cells....

 

[jindon]

Much Obliged. But will this return true even If I have some blank cells?

Because SOME Blank cells are OK, I just don't want non-numeric cells or ALL blank cells....

Confusing...

If (Application.CountBlank(Range) <> Range.Cells.Count) * _
   (Application.CountA(Range) = Application.Count(Range)) Then
        'Process
End If

 

[Checho78]

Using isNull(Range) will tell you if any cell in the range has a value, otherwise the Range.text property will return "" if all cells are blank.