How to Use VBA to Check if a Cell is Empty in MS Excel
Contents
Introduction to Cells
In Microsoft Excel, a cell is nothing but an intersection of a column and a row. This cell is referenced by its row number and column letter. A cell can hold a formula/data directly. It can be formatted with font styles and colors or background colors too.
In the image above, cell B4 is selected. It is the intersection of row number “4” and column letter “B.” Since the value of the cell does not start with an “=”, it means that the cell is holding some data directly. Here the data held is “110.”
Empty Cells in Excel
If the cell does not have any value, it is said to be empty. There’s a chance that a cell has the same font color and background color, but with some data. In that case, it may look empty but actually isn’t. So, to find this, we have to select the cell and check the formula bar.
Here the selected cell D9 looks blank but has a value “India.” It is not visible on the cell because the color of the font in that cell is white, which is equal to its background color.
ISBLANK Formula
Microsoft Excel offers a formula named ISBLANK which returns/provides a Boolean result stating whether the value of that cells is blank or not.
Syntax:
ISBLANK( <value> )
Where <value>
can be a reference to a cell.
In the above images, the selected cells hold the formula as in the formula bar referring to the cells B7 (first image) and C8 (second image) respectively. The false and true values are returned depending on the values in the cells referred as parameters.
The selected cells hold the formula seen in the formula bar referring to the cells B7 (first image) and C8 (second image) respectively. The false and true values are returned depending on the values in the cells referred as parameters.
Range of Cells in Excel
In Excel a range is a set of continuous cells which can be a part of one or more columns or one or more rows or both. For example, in this image above Range(“A1:B12”) can be termed as a range.
Empty Cells in a Range
If there is a need to find the empty cells in a huge range of cells, it might be time consuming and tiring to select each cell and find this manually.
VBA to Find Empty Cells
VBA offers an inbuilt function called “IsEmpty” to do this for us.
Syntax
<Var> = IsEmpty ( <expression> )
Where <var>
is any Boolean variable that can hold the end result value (return value) of the function and <expression>
is any value that needs to be checked if empty.
For Example:
Select a Cell and Display if it is Empty
Sub empty_demo() ' select an empty cell Cells(1, 5).Select ' display a msg to cross check if the selected cell is empty MsgBox IsEmpty(Cells(1, 5).Value) End Sub
The same with a non-empty cell:
Sub empty_demo() ' select a non-empty cell Cells(1, 1).Select ' display a msg to cross check if the selected cell is empty MsgBox IsEmpty(Cells(1, 1).Value) End Sub
Another Simple Program to Check if a Cell is Empty
Sub demo2() ' check if the value of a particular cell is nothing or "" ' if there is a value, the value is displayed . If not, a statement is displayed If Cells(3, 4).Value = "" Then MsgBox "The cell in 3rd row and 4th col is empty" Else MsgBox Cells(3, 4).Value End If End Sub
This program looks if the value of a specific cell is empty using just the “”. The same can also be done on several cells using a loop/range.
VBA – Find Empty Cells in a Range
It is possible to find the cells that are empty in a range of cells. Once a range is defined, we can loop through each cell in the range. As we loop, we can check if the cell is empty.
Program to Count the Number of Empty Cells in a Range of Cells
In this program:
- We declare a count variable and initialize it to “0.” This will help us count the number of empty cells in a range.
- We define a range in an Excel worksheet.
- Then we loop through the cells in it using a “for each” loop. Inside the loop, we check if the cell is empty/blank using the inbuilt VBA function “ISEMPTY()”.
- If so, the value of the “cnt” variable is incremented by “1.” Once we come out of the loop, we display the value of “cnt” as the number of empty cells in the declared range.
Sub empty_demo() ' declare a range Dim myrange 'declare a variable to store count Dim cnt ' define the range and initialize count myrange = Range("A1:A20") cnt = 0 'loop through each cell of the range For Each cell In myrange ' if the cell is empty , we increment the value of cnt variable by 1 If IsEmpty(cell) Then cnt = cnt + 1 End If Next cell ' display the number of empty cells MsgBox "There are " &amp; cnt &amp; " empty cells in the mentioned range. " End Sub
Color the Empty Cells in a Range of Cells Without Defining Any Range
In this program, instead of using a range, we use a nested “for loop” to iterate through every row in every column. The outer loop iterates through the columns while the inner loop iterates through the rows. The upper and lower bounds of the desired range/table are already provided in the “for loop” definition. Inside the inner loop, as in the previous example, we check if the cell is empty and add a number to the “cnt” variable’s value if so. Here, in addition, we also color those empty cells that are identified within the inner loop’s condition.
Sub empty_demo_1() ' declare variables to indicate, row, col and counter Dim r, c, cnt ' intialize the variables r = 1 c = 1 cnt = 0 ' loop through each col in the worksheet till we reach col no "2" For c = 1 To 2 ' loop through each row in the worksheet till we reach row no "20" For r = 1 To 20 ' if the cell ( intersection of the row and col) is empty , we increment the value of cnt variable by 1 If IsEmpty(Cells(r, c).Value) = True Then cnt = cnt + 1 ' Color the cells in yellow just to identify Cells(r, c).Interior.Color = 65535 End If Next r Next c ' display the number of empty cells MsgBox "There are " &amp;amp; cnt &amp;amp; " empty cells in the mentioned range. " End Sub
Using IsEmpty() for Variable Values
This program checks if a variable is empty and displays the result.
Sub demo3() ' declare a variable and do not initialize Dim var1 ' check if variable has value If IsEmpty(var1) = True Then ' statement if empty Debug.Print "Var1 is Empty" Else ' statement if not empty Debug.Print "Var1 has a value " &amp;amp; var1 End If End Sub
Output would be:
“Var1 is Empty”
Sub demo3() ' declare a variable and initialize it Dim var1 var1 = "This is a good day" ' check if variable has value If IsEmpty(var1) = True Then ' statement if empty Debug.Print "Var1 is Empty" Else ' statement if not empty Debug.Print "Var1 has a value. " &amp;amp; var1 End If
Output would be:
“Var1 has a value. This is a good day”
Conclusion
Apart from cells and variables, the IsEmpty() function offered by VBA can be used for arrays and many such objects. However, these are not elaborated here as the focus of this article is on checking if a cell is empty. The “” ( empty double quotes) can also be used to check if the cell(s) are empty. This is just a replacement for the IsEmpty() function and straightforward too. At times, we get stuck into a situation where nothing works out to validate if a cell is empty. In that case we can also try the len() function. The length of the cell value is “0” if it is actually empty.
Check out our other articles on the specific functions to know much about their usage.