Check if all cells in range are empty?

Cymae

Board Regular
Joined
Oct 19, 2008
Messages
70
I have an if statement as follows:

Code:
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:

Code:
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Zeros are not the same as nothing.

If you want to make sure each cell is either blank or is zero then:

Code:
If Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol)) = "" Or Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol)) = 0 Then

The If doesn't need a ( in VBA like it does in a formula.
 
Upvote 0
I made a bit of a mistake in my code, sorry, what I meant is this:

Code:
If IsEmpty(Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol - 1))) = False Then

So I want it to look along my row of cells, and ONLY if there's something in it do i want it to go into the if. The problem I'm having is that it's processing all rows for some odd reason :S
 
Upvote 0
IsEmpty function can not be used in the range that has multiple cells.

Application.CountBlank will return the number of cells that are Blank.
 
Upvote 0
IsEmpty function can not be used in the range that has multiple cells.

Application.CountBlank will return the number of cells that are Blank.

Jindon, you always seem to come to my rescue :biggrin: if there was a reputation system on this forum, I'd add to yours daily.

Thank you very much ;)
 
Upvote 0
Perhaps something like this:
Code:
If 0 < Evaluate("=SUMPRODUCT(LEN(" & myRange.Address & "))") Then
    MsgBox "Some cell in myRange has non-zero length."
Else
    MsgBox "All cells in myRange have zero-length entries."
End If
It will count formulas that evaluate to "" the same as blanks.
 
Upvote 0
Perhaps something like this:
Code:
If 0 < Evaluate("=SUMPRODUCT(LEN(" & myRange.Address & "))") Then
    MsgBox "Some cell in myRange has non-zero length."
Else
    MsgBox "All cells in myRange have zero-length entries."
End If
It will count formulas that evaluate to "" the same as blanks.

That's good, but I need to check if Any cells have non-zero length or if NO CELLS have non-zero length. I don't care if one or many have non-zero, I want it to evaluate true only if all of them are empty

At the risk of sounding a bit dumb, how do I use the CountBlank function on my range? I can't find an example of it in VBA, only as a workbook function
 
Upvote 0
try
Code:
With Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol - 1))
    If Application.CountBlank(.Cells) = .Cells.Count Then
        MsgBox "All blank"
    Else
        MsgBox "Not all blank"
    End If
End With
 
Upvote 0
Code:
If 0 = Evaluate("=SUMPRODUCT(LEN(" & myrange.Address & "))") Then
    MsgBox "All cells are empty."
Else
    If 0 = Evaluate("=PRODUCT(1-(LEN(" & myrange.Address & ")=0))") Then
        MsgBox "Some empty, some not"
    Else
        MsgBox "No cells are empty"
    End If
End If
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top