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?

 

[jindon]

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

What do you mean ?

 

[Cymae]

try

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

Would this cese to work if some cells were hidden? That's the only thing I can think of that I havn't mentioned...

To clarify:

I have a spreadsheet with a whole bunch of rows, and I'm creating sum statements for the final column on the fly. I want it to skip rows that are empty pretty much. I am not looking at the whole row however, rather something like 8 cells in to the total row -1. What I want it to do is if it finds a selection of cells with nothing in it, it does not put the sum statment in the final row.

Is that a bit clearer?

 

[jindon]

I'm not sure with the hidden cells.
Why don't you give that a try for yourself?

test if with hidden cells and w/o hidden cells...

If you don't want to count hidden cells then

With Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol - 1))
    For Each myArea in .SpecialCells(12).Areas
        If Application.CountBlank(myArea) <> myArea.Cells.Count Then
            MsgBox "Not all blank"
            Exit Sub
        End If
End With
MsgBox "All blank"

 

[Cymae]

Tested with test sub as follows:

Sub temp()
    Sheet1.Select
    With Range(Cells(1, 1), Cells(1, 9))
                    .Select
                    If Application.CountBlank(.Cells) = .Cells.Count Then
                        Cells(1, 10).Value = "blank"
                    Else
                        Cells(1, 10).Value = "=sum(" & Cells(1, 1).Address(0, 0) & ":" & Cells(1, 9).Address(0, 0) & ")"
                    End If
    End With
    Sheet2.Select
    With Range(Cells(1, 1), Cells(1, 9))
                    .Select
                    If Application.CountBlank(.Cells) = .Cells.Count Then
                        Cells(1, 10).Value = blank
                    Else
                        Cells(1, 10).Value = "=sum(" & Cells(1, 1).Address(0, 0) & ":" & Cells(1, 9).Address(0, 0) & ")"
                    End If
    End With
End Sub

Test results:
When Cells are hidden they are still taken into account in the countblank function. Now I gotta work out why this isn't working on my spreadsheet Thanks all

 

[jindon]

See my last post. (Edited)

 

[Cymae]

I do not know why, but I closed the workbook and opened it again without changing the code....and now it works

 

[Cymae]

On this train of thought, can I use IsNumeric across a range?

 

[jindon]

Tested with test sub as follows:

Sub temp()
    Sheet1.Select
    With Range(Cells(1, 1), Cells(1, 9))
                    .Select
                    If Application.CountBlank(.Cells) = .Cells.Count Then
                        Cells(1, 10).Value = "blank"
                    Else
                        Cells(1, 10).Value = "=sum(" & Cells(1, 1).Address(0, 0) & ":" & Cells(1, 9).Address(0, 0) & ")"
                    End If
    End With
    Sheet2.Select
    With Range(Cells(1, 1), Cells(1, 9))
                    .Select
                    If Application.CountBlank(.Cells) = .Cells.Count Then
                        Cells(1, 10).Value = blank
                    Else
                        Cells(1, 10).Value = "=sum(" & Cells(1, 1).Address(0, 0) & ":" & Cells(1, 9).Address(0, 0) & ")"
                    End If
    End With
End Sub

Test results:
When Cells are hidden they are still taken into account in the countblank function. Now I gotta work out why this isn't working on my spreadsheet Thanks all

Do you want to count only the Numbers in the cell ?

That is Application.Count.

If Application.Count(.Cells) = 0 Then
    MsgBox "No numeric cell"

 

[Cymae]

Do you want to count only the Numbers in the cell ?

That is Application.Count.

If Application.Count(.Cells) = 0 Then
    MsgBox "No numeric cell"

I want to skip the line if all cells are empty or if ANY cell is non-numeric.

 

[jindon]

Can you explain what you are trying to do in WORDS ?