With Excel formulas, we are already automating our work. Adding VBA to this, we can get repetitive actions automated.
We can find the non-empty values in our file with formulas such as COUNTA, ISBLANK, or similar. But we can do this even more efficiently with VBA. In the example below, we will show how to do this.
Checking Non-Empty Cells
For our example, we will use the list of random numbers located in the first two columns:
The two different things that we can search for in terms of empty values are cells and ranges. For the first thing, we will inspect a certain cell. We will open our VBA by pressing ALT + F11, and then choose Insert tab >> Module on the window that appears:
Having our Module opened in the new window on the right side, and let’s say that we want to search if cell A4 is empty, we will insert the following code:
|
1 2 3 4 5 6 7 8 9 |
Sub CheckNonEmptyValue() Dim j As Variant j = Range("A4") ' Check if the variable is not empty using IsEmpty function If Not isEmpty(j) Then MsgBox "The variable is not empty." Else MsgBox "The variable is empty." End If |
This is what our code looks like in the module:
It is a simple code that declares our variable (j as a Variant), then sets it to the value we want (cell A4 in our case), and then uses the IF function in combination with isEmpty to see if the value in our variable is empty or not.
Checking Non-Empty Ranges
For the next example, if we want it find out if there are any empty cells in our range, we can use the following formula:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub CheckEmptyRange() Dim myRange As Range Dim f As Integer ' Set the range you want to check Set myRange = Range("A2:B8") f = WorksheetFunction.CountBlank(myRange) ' Check if there are empty cells in our range If f = 0 Then MsgBox "There are no empty cells in our range." Else MsgBox "There are empty cells in our range." End If End Sub |
In the module, this is what this formula looks like:
What this formula does, is that it simply counts the number of empty cells in our range. It uses the IF Else clause to show two different messages: one that there are no empty cells in our range, and the other that there are empty cells in our range, depending on the status.
In our example, as we do have empty cells in our range when we execute the code by pressing F5 on our keyboard, this is the message that we will have on our screen:
If we wanted to highlight all the empty cells in our range, we could do that as well, using IF Else again, but wrapped in the For Each syntax. Our code will be slightly different, and we have to choose to do something with the empty cells, in our case, we will color them.
For all the non-empty cells, we will remove any background color, just to make sure that our code removes the color from cells that were populated later on.
Our code will look like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub DetectEmptyCells() Dim rng As Range Dim cell As Range ' Replace "A2:B8" with the range you want to check for empty cells Set rng = Range("A2:B8") For Each cell In rng If IsEmpty(cell) Then cell.Interior.Color = RGB(255, 20, 20) Else cell.Interior.ColorIndex = xlNone End If Next cell End Sub |
This code first declares two variables, rng, and cell, both of them as Range. Then we set our range to be equal to our desired range. This is the only part of the code that needs to be changed to adjust if needed.
Then we define the For Each Loop that checks every cell in our range if it is empty or not. If it is, then it colors it into the variant of red color. If not, then it clears the background color for every cell in our range.
When we execute our code by pressing F5 on our keyboard, this is the result that we will end up with:
