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,399

    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
    Excel & Access blog
    All Excel Functions
    Training
    (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,747

    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,399

    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
    Excel & Access blog
    All Excel Functions
    Training
    (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,399

    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
    Excel & Access blog
    All Excel Functions
    Training
    (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