Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Determine if a cell is null (2002)

    Hi,

    I'm writing and if/than statement based on whether a cell is null but can't get the syntax correct. IE if range("a1") is null then do XYZ else do ABC. How do I write the code so that excel will determine if the named range is null?

    Thanks,
    Leesha

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine if a cell is null (2002)

    If the named MyCell is one cell, then you could use something like this:

    <code>
    If Range("MyCell").Value = "" Then
    MsgBox "Empty"
    Else
    MsgBox "Not Empty"
    End If
    </code>

    If the range could consist of more than one cell and is named MyRange, then you could use something like this:

    <code>
    If Application.WorksheetFunction.CountA(Range("MyRang e")) = 0 Then
    MsgBox "Empty"
    Else
    MsgBox "Not Empty"
    End If
    </code>
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Determine if a cell is null (2002)

    If you want to differentiate between truly "empty" (nothing in the cell) and a cell with a null you can modify Legare's code:

    <pre> If IsEmpty(Range("MyCell")) Then
    MsgBox "Empty"
    ElseIf Range("MyCell").Value = "" Then
    MsgBox "Null"
    Else
    MsgBox "Not Empty"
    End If</pre>


    or even (to differentiate a null and cell with just spaces)
    <pre> If IsEmpty(Range("MyCell")) Then
    MsgBox "Empty"
    ElseIf Range("MyCell").Value = "" Then
    MsgBox "Null"
    ElseIf trim(Range("MyCell").Value) = "" Then
    MsgBox "Just Spaces"
    Else
    MsgBox "Not Empty"
    End If</pre>



    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •