Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Check if all cells in range are empty?

This is a discussion on Check if all cells in range are empty? within the Excel Questions forums, part of the Question Forums category; I have an if statement as follows: Code: If IsEmpty(Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol))) Then i did a select to make ...

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Location
    Brisbane, Australia
    Posts
    69

    Default Check if all cells in range are empty?

    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?

  2. #2
    Board Regular
    Join Date
    Dec 2004
    Posts
    2,070

    Default Re: Check if all cells in range are empty?

    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.

  3. #3
    Board Regular
    Join Date
    Oct 2008
    Location
    Brisbane, Australia
    Posts
    69

    Default Re: Check if all cells in range are empty?

    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

  4. #4
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: Check if all cells in range are empty?

    IsEmpty function can not be used in the range that has multiple cells.

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

  5. #5
    Board Regular
    Join Date
    Oct 2008
    Location
    Brisbane, Australia
    Posts
    69

    Default Re: Check if all cells in range are empty?

    Quote Originally Posted by jindon View Post
    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 if there was a reputation system on this forum, I'd add to yours daily.

    Thank you very much

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,683

    Default Re: Check if all cells in range are empty?

    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.

  7. #7
    Board Regular
    Join Date
    Oct 2008
    Location
    Brisbane, Australia
    Posts
    69

    Default Re: Check if all cells in range are empty?

    Quote Originally Posted by mikerickson View Post
    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

  8. #8
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: Check if all cells in range are empty?

    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

  9. #9
    Board Regular
    Join Date
    Oct 2008
    Location
    Brisbane, Australia
    Posts
    69

    Default Re: Check if all cells in range are empty?

    No error, but it's still processing my blank cells...

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,683

    Default Re: Check if all cells in range are empty?

    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

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com