VBA isblank function

[chenchanjuan]

Hello,

I have a question about the isblank function.

I have code like this:

Sub checkempty()

dim row as integer
For row=1 to 10
if [and(isblank(range(cells(row,1),cells(row,5)))] = T then
msgbox "all empty"
else msgbox "not all empty"
end if

next
End sub

If I use the isblank function without loop, say
if [and(isblank(A1:A5))] = T then blahblah

it works. Do you know how to add a loop into the isblank function?

Thank you so much
Cathy

 

[NateO]

Hello,

You can't use brackets on variable ranges. Try the following:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> checkempty()
<SPAN style="color:darkblue">Dim</SPAN> cl <SPAN style="color:darkblue">As</SPAN> Range
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> cl <SPAN style="color:darkblue">In</SPAN> [a1:a10]
    <SPAN style="color:darkblue">If</SPAN> Evaluate("SUM(LEN(" & cl.Resize(, 5).Address _
        & "))") <SPAN style="color:darkblue">Then</SPAN>
        MsgBox "Not All Empty"
        Else: MsgBox "All Empty"
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

 

[Zack Barresse]

As an option, and if NateO could confirm this (please? ) that you may be able to use the IsEmpty instead of an isblank type of test.

 

[NateO]

Hello Zack,

Not on a multi-cell range (which Cathy is asking about). Her functional code ([and(isblank(A1:A5))]) is an array function as is the one I posted.

IsEmpty() won't evaluate all of the cells in one pass, you'd need a second loop.

 

[tusharm]

True, but a neat little trick that bypasses the idiosyncracies of the Evaluate function would be to check if the result of the COUNTA function is >0.

Hello Zack,

Not on a multi-cell range (which Cathy is asking about). Her functional code ([and(isblank(A1:A5))]) is an array function as is the one I posted.

IsEmpty() won't evaluate all of the cells in one pass, you'd need a second loop.

 

[Zack Barresse]

True, but a neat little trick that bypasses the idiosyncracies of the Evaluate function would be to check if the result of the COUNTA function is >0.

That's a good call. Do either of you guys know a strictly VBA call that will discern the presence of a value in a cell w/o looping or reverting to native Excel functions? Just curious.

 

[NateO]

True, but a neat little trick that bypasses the idiosyncracies of the Evaluate function would be to check if the result of the COUNTA function is >0.

Which in turn is true, and a better way to proceed, that's what it's there for eh. It might look like:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> checkempty()
<SPAN style="color:darkblue">Dim</SPAN> cl <SPAN style="color:darkblue">As</SPAN> Range
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> cl <SPAN style="color:darkblue">In</SPAN> [a1:a10]
    <SPAN style="color:darkblue">If</SPAN> WorksheetFunction.CountA(cl.Resize(, 5)) <SPAN style="color:darkblue">Then</SPAN>
        MsgBox "Not All Empty"
        Else: MsgBox "All Empty"
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Apparently I wasn't seeing the forest through the trees! Maybe I should stop breaking the board and think more about my Excel posts!

 

[NateO]

Hello again Zack,

Do either of you guys know a strictly VBA call that will discern the presence of a value in a cell w/o looping or reverting to native Excel functions? Just curious.

A single cell, isEmpty(). E.g., MsgBox IsEmpty([b2])

You also have SpecialCells(xlblanks) at your disposal and Autofilter capabilities. What's the issue with using Excel functions in Excel?

Did you have a more specific example in mind?

 

[Zack Barresse]

Did you have a more specific example in mind?

Actually no. Just sheer curiosity. I thought I'd ask the guru's while I had ya on the line. Looking at it, I'm not sure there would really be a better way than to use the CountA function. That's pretty fast.

Thanks! TTYL

 

[Nimrod]

Hope you guys don't mind me throwing in another suggestion....

Sub CheckForBlanks()
For Rw = 1 To 10
BLankCellCount = 0
On Error Resume Next
BLankCellCount = Range("A" & Rw & ":E" & Rw).SpecialCells(xlConstants, 3).Count
If BLankCellCount > 0 Then
MsgBox "Row " & Rw & " Not Blank"
Else
MsgBox "Row " & Rw & " is Blank"
End If
Next Rw
End Sub