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

0 votes

Why does the result in cell A1 return "2" even if there are 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 the results return 0 even if the cells were blank.

Jan 15 in Others by Kithuzzz
• 28,900 points
44 views

1 answer to this question.

0 votes

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
answered Jan 15 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

How to show a row in excel if there is a X

FILTER Function: "filters" a range of data ...READ MORE

answered 6 days ago in Others by narikkadan
• 53,520 points
19 views
0 votes
1 answer

Formula to count if a text is written in a cell

Use SCAN, like this: =ArrayFormula(IF(A1:A10="",,SCAN(-1,A1:A10,LA ...READ MORE

answered 4 days ago in Others by narikkadan
• 53,520 points
27 views
0 votes
1 answer

How to check if array is multidimensional or not?

Since the 'second dimension' could be just ...READ MORE

answered Nov 5, 2018 in Others by DataKing99
• 8,240 points
4,264 views
0 votes
1 answer

What is a name function in JavaScript & how to define it?

A named function declares a name as ...READ MORE

answered Mar 7, 2019 in Others by Frankie
• 9,830 points
2,276 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
533 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,415 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,960 points
117 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
300 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 53,520 points
1,188 views
0 votes
1 answer

Is there a way to quickly check what attribute the data is in a cell in a spreadsheet?

Excel will automatically make assumptions about the ...READ MORE

answered Jan 8 in Others by narikkadan
• 53,520 points
56 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP