Ignore Formula in vba by check if cells in range are empty

[VeKa27]

Hi,
In vba i want to check if there is data in cells in a range. The problem is that in some cells within that range there are formulas that returns nothing (like: ""). So the range is visual empty because the formula returns nothing.

I tried with this but it does not work:

Sub TestThisCode()
Dim Cell As Range
For Each Cell in Range("B10:E15")
If Cell.Value = True Then
Msgbox "Not all Empty"
GoTo stop
Else
Msgbox "All Empty"
End If
Next Cell
Stop:
End Sub

So i want my code to stop if there is any data in one of the cells but the code has to ignore formulas that returns ""

Thanks to think with me..

 

[JLGWhiz]

Test for the lenght of the cell value:

If Len(Cell.Value) > 0 Then
    MsgBox "Cell has visible value"
End If

 

[mikerickson]

I'm not sure what you mean by "ignore formulas that return """

Do you want the code to treat those cells as if they are empty (JLGWhiz's code does that)
or do you the code to treat those cells as if they were full

If Cell.Formula = vbNullString then

 

[VeKa27]

To JLGWhiz: it works but only if there is data found in the first cell (B10). Otherwise the code loops and gives for every blanc cell the message "All Empty" until the code founds a cell with data. Then the loop stops. but also there is this problem: i forgot to mention this line in the code after Msgbox "All Empty"
Range("A10:A15").EntireRow.hidden = True


To mikerickson: yes, i want the code to threat the formula cells as if they are empty.

To be clear, i want to check the range if there is visible data in it and if not, the code may hide the rows. If there is any visible data in the range, the code has to give the msgbox "Not all empty" and stop without hiding the rows

 

[JLGWhiz]

I am not sure what you are expecting. This code:

Sub TestThisCode()
Dim Cell As Range
    For Each Cell In Range("B10:E15")
        If Len(Cell.Value) > 0 Then
            MsgBox "Not all Empty"
            GoTo Stopp:
        Else
            MsgBox "All Empty"
        End If
    Next Cell
Stopp:
End Sub

seemed to work as expected in test set up. I hid rows 10:15 but had formulas in B10:B15, with C10:E15 blank. The formulas all returned "" value. The "A;ll Empty" message was returned for the entire range B10LE15, then I put a value in D10 and it only displaye the All Empty message 2 times before the Not All Empty message and the code teminated.

If you expect something different than that, please explain.

 

[VeKa27]

Not yet as expected..

The second messagebox, you can delete, it was just for me to control my code. In that place put the line to hide the rows 10-15.

my goal is that if there is visible data in one of the cells, the code must just give the message "Not all Empty" and has to stop. BUT: in that case it may NOT hide the rows..

 

[JLGWhiz]

Maybe this is what you want.

Sub TestThisCode()
Dim Cell As Range
    For Each Cell In Range("B10:E15")
        If Len(Cell.Value) > 0 Then
            MsgBox "Not all Empty"
            Rows("10:15").Hidden = False
            GoTo Stopp:
        End If
    Next Cell
    MsgBox "All Empty"
Stopp:
End Sub

 

[JLGWhiz]

BTW, you should not use key words like Stop in your code for anything other than their intended use. Stop is a command to stop the macro the same as putting in a break point in with the editor.
See here: Stop Statement - Visual Basic

 

[VeKa27]

Yes, yes, yes,...!!! That is exact wat i was looking for... (happy as a little child )

Thank you very much. Glad you helped me out of this one, very thankfull..

(gonna read the Stop Statement.. thanks)

 

[JLGWhiz]

Yes, yes, yes,...!!! That is exact wat i was looking for... (happy as a little child )

Thank you very much. Glad you helped me out of this one, very thankfull..

(gonna read the Stop Statement.. thanks)

Happy to help. You might also want to review and print a copy of this: Keywords - Visual Basic
regards, JLG