Is empty is not working

Question

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.

Do you want to test if ***all*** the cells in the range are empty or if ***any*** of the cells in the range are empty ??? — Gary's Student, Mar 03 '17 at 16:24
If there are two different ways for them then please tell me both. Anyways mine to see if any one of the cell is empty or not — Mansh05, Mar 03 '17 at 16:27
Possible duplicate of [Excel Visual Basic - detect if range is empty](http://stackoverflow.com/questions/10811121/excel-visual-basic-detect-if-range-is-empty) — GSerg, Apr 15 '17 at 09:29

score 2 · Accepted Answer · edited May 23 '17 at 10:31

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.

score 0 · Answer 2 · answered Mar 03 '17 at 17:09

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

Is empty is not working

2 Answers2

Linked