CODEWITHSUNDEEP
excelvba
Mansh05
Mansh05

Reputation: 63

Is empty is not working

I m writing a program and it is not working. I will write a simple version of it

Dim rng as range
Dim message as integer 
Set rng = Range(cells(1,1), cells (4,1))
If isempty(rng) then
   mess = msgbox ("hello", vbyesno, "hello")
      If mess = 6 then
       Msgbox "hello"
       Else
       Msgbox "bye"
       End if
Else
    Msgbox "haha"
End if

This is the program, But when the cell is empty or if there is a value in the cell then it executes the else statement, that is "haha" and howsoever doesn't execute the first statement although it may be true. Why is it happening and what am i possibly doing wrong while writing the program.

Upvotes: 2

Views: 3369

Answers (2)

GSerg
GSerg

Reputation: 78155

IsEmpty is not an Excel-specific function that detects empty cells.
It's a part of the VB6/A language and it checks if the variable of type Variant contains a special value, Empty.

It happens to work when checking contents of a single Excel cell because the expression IsEmpty(cell) is understood as IsEmpty(cell.Value), and cell.Value of a single cell returns a single Variant that may have the special value of Empty which IsEmpty detects.

In other cases it does not work:

  • When you pass a multicell range to IsEmpty, the range.Value returns an array of Variants. An array itself is never Empty, even when all its elements contain Empty.
  • When you pass a Range variable that is Nothing, IsEmpty cannot detect it either, because Empty is not Nothing.

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

You can use CountA to see how many cells in a certain range are not empty.

CountA documentation on MSDN

Option Explicit

Sub checkEmptyRange()

Dim Rng As Range

With Worksheets("Sheet1") '<-- modify "Sheet1" to your sheet's name
    Set Rng = .Range(.Cells(1, 1), .Cells(4, 1))
    If WorksheetFunction.CountA(Rng) > 0 Then
        If MsgBox("hello", vbYesNo, "hello") = vbYes Then
           MsgBox "hello"
        Else
           MsgBox "bye"
        End If
    Else
        MsgBox "haha"
    End If
End With    

End Sub

Upvotes: 0

Related Questions