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

VeKa27

New Member
Joined
Sep 11, 2015
Messages
45
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:

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

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Test for the lenght of the cell value:

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917
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

VBA Code:
If Cell.Formula = vbNullString then
 

VeKa27

New Member
Joined
Sep 11, 2015
Messages
45
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I am not sure what you are expecting. This code:
VBA 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

New Member
Joined
Sep 11, 2015
Messages
45
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Maybe this is what you want.

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Sep 11, 2015
Messages
45
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,603
Members
414,393
Latest member
Vignesh Mechz

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
Top