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, 2023 in Others by Kithuzzz
• 38,000 points
859 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, 2023 by narikkadan
• 63,600 points

Related Questions In Others

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, 2023 in Others by narikkadan
• 63,600 points
563 views
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 Feb 2, 2023 in Others by narikkadan
• 63,600 points
413 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 Feb 4, 2023 in Others by narikkadan
• 63,600 points
505 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,250 points
6,003 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
1,260 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
3,697 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
• 23,260 points
975 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,690 points
1,090 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
• 63,600 points
3,762 views
0 votes
1 answer

How to set a column as a range variable as long as there is data in it?

Try this: Dim rng As Range Set rng = ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,600 points
625 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