How to check if a cell is empty in a range variable?-CodePudding

Why does the result in cell A1 returns "2" even if there is no values in either of the B1 to B3 cells?

Sub CheckEmpty()

Dim rng As Range

Set rng = Range("$B$1:$B$3")
Debug.Print rng.Address

If IsEmpty(Range(rng.Address)) Then
    Range("A1").Value = "1"
    Else
    Range("A1").Value = "2"
End If

End Sub

I've tried including values in either cell b1, b2 or b3 but they results returns 0 even if the cells were blank.

CodePudding user response：

use WorksheetFunction.CountA() (https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.counta) function

If WorksheetFunction.CountA(rng) = 0 Then
    Range("A1").Value = "1"
Else
    Range("A1").Value = "2"
End If