Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading