Results 1 to 10 of 10

VBA isblank function

This is a discussion on VBA isblank function within the Excel Questions forums, part of the Question Forums category; Hello, I have a question about the isblank function. I have code like this: Sub checkempty() dim row as integer ...

  1. #1
    New Member
    Join Date
    Apr 2004
    Location
    San Francisco
    Posts
    20

    Default VBA isblank function

    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

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: VBA isblank function

    Hello,

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

    Sub checkempty()
    Dim cl As Range
    For Each cl In [a1:a10]
    ****If Evaluate("SUM(LEN(" & cl.Resize(, 5).Address _
    ********& "))") Then
    ********MsgBox "Not All Empty"
    ********Else: MsgBox "All Empty"
    ****End If
    Next
    End Sub

  3. #3
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,478

    Default

    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.
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: VBA isblank function

    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.

  5. #5
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,924

    Default Re: VBA isblank function

    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.
    Quote Originally Posted by 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.

  6. #6
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,478

    Default Re: VBA isblank function

    Quote Originally Posted by 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.
    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.
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: VBA isblank function

    Quote Originally Posted by Tushar
    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:

    Sub checkempty()
    Dim cl As Range
    For Each cl In [a1:a10]
    ****If WorksheetFunction.CountA(cl.Resize(, 5)) Then
    ********MsgBox "Not All Empty"
    ********Else: MsgBox "All Empty"
    ****End If
    Next
    End Sub


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

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: VBA isblank function

    Hello again Zack,
    Quote Originally Posted by firefytr
    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?

  9. #9
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,478

    Default Re: VBA isblank function

    Quote Originally Posted by NateO
    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
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  10. #10
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: VBA isblank function

    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

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

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