How to Check If an Array Is Empty with VBA in Excel (3 Methods)

Below is a dataset on gangsters’ secret names in the Serial No, Gangster, and Pseudonym columns.

Set an Array with VBA in Excel

Steps:

Go to the Developer tab.
Click on Visual Basic from the ribbon.

Click on the Insert tab from the Microsoft Visual Basic for Applications window.
Select Module from the available options.

Enter the following VBA code:

Sub SetArrayRange()
    Dim MyArray() As Variant
    Dim G_sters As String
ReDim MyArray(Range("D5:D14").Rows.count)
i = 1
For Each j In Range("D5:D14")
    MyArray(i) = j
    i = i + 1
Next j
For Pseudonym = 1 To UBound(MyArray)
        Debug.Print MyArray(Pseudonym)
    Next Pseudonym
End Sub

Code Explanation

I have used SetArrayRange() as Sub_Procedure and Dim to declare variables. I have declared MyArray(() as a Variant that can be defined with any kind of value and G_sters as a string value. Then, I applied the For loop in the range D5:D14 to print the values in that range.

Go to the Immediate Window option in the View tab to get the array output.

Alternatively, you can press CTRL + G to have the Immediate Window.
You can see the Immediate window at the bottom.

Click on the Run or F5 button to execute the code.

Thus, we can see that defined array in the Immediate window.

Method 1 – Applying VBA with the JOIN Function

Steps:

Open a VBA module following the above-mentioned process.
Enter the following code:

Sub CheckEmptyArray()
    Dim MyArray() As Variant
    Dim G_sters As String
  ReDim MyArray(Range("D5:D14").Rows.count)
i = 1
For Each j In Range("D5:D14")
    MyArray(i) = j
    i = i + 1
Next j
    G_sters = Join(MyArray)
    x = Len(G_sters)
   If x > Range("D5:D14").Rows.count Then
Debug.Print "Array is not empty"
Else
Debug.Print "Array is empty"
End If
End Sub

Code Explanation

I have used CheckEmptyArray() as Sub_Procedure and Dim to declare variables. I have declared MyArray() as a Variant, which can be defined with any kind of value, and G_sters as a string value. Afterward, I applied a For loop in the range D5:D14 to have the values in that range. The JOIN function here gives a single string combining all the substrings of that array. I also used the LEN function VBA to return the number of characters of the range. If the number of characters exceeds the number of rows, we can say that the array is not empty. If it gets equal to each other, the array is empty.

Run the code by clicking on the Run or F5 button.

As our array has values, we can see in the Immediate window that the Array is not empty.
Consider an alternate case where there is no value in the array.

If we run the code, we can see the output Array is empty in that case.

Method 2 – Using the VBA ISEMPTY Function to Check If an Array Is Empty

Steps:

Follow the above-mentioned process to open a VBA module.
Enter the following VBA code:

Sub CheckWithIsEmpty()
    Dim MyArray() As Variant
    Dim G_sters As String
    Dim count As Integer
ReDim MyArray(Range("D5:D14").Rows.count)
i = 1
For Each j In Range("D5:D14")
    MyArray(i) = j
    i = i + 1
Next j
   count = 0   
  For i = LBound(MyArray) + 1 To UBound(MyArray)
        If IsEmpty(MyArray(i)) = True Then
    count = count + 1
   End If
 Next
If Range("D5:D14").Rows.count - count > 0 Then
Debug.Print "Array is not empty"
Else
Debug.Print "Array is empty"
End If
End Sub

Code Explanation

If you look closer, you will see that the first portion of the code is almost the same as the above one. I have additionally used another variable named count. The LBOUND function returns the lowest value in the range, and UBOUND returns the highest. The count variable is looped through the lowest to the highest value of the range to check if there is any empty row in that array with the help of the ISEMPTY function. If any empty value is found, that variable’s value increases by 1. In addition to that, Range.Rows.count property calculates the number of characters in that range. Then, the number of characters is obtained by the Range.Rows.count property is subtracted with the count value. If the difference between these values is greater than 0, we can say that the array is not empty. Otherwise, the array is empty.

Run the code by clicking on the Run or F5 button.

We can verify the array.

Method 3 – Using a Manual Procedure to Check If an Array Is Empty

Steps:

Enter the following code:

Sub CheckManually()
    Dim MyArray() As Variant
    Dim G_sters As String
    Dim count As Integer
ReDim MyArray(Range("D5:D14").Rows.count)
i = 1
For Each j In Range("D5:D14")
    MyArray(i) = j
    i = i + 1
Next j
   count = 0   
  For i = LBound(MyArray) + 1 To UBound(MyArray)
        If MyArray(i) = "" Then
    count = count + 1
   End If
 Next
If Range("D5:D14").Rows.count - count > 0 Then
Debug.Print "Array is not empty"
Else
Debug.Print "Array is empty"
End If
End Sub

Code Explanation

This code is almost similar to the code used in the previous method. The main difference is that I used the ISEMPTY function in the previous function to check the empty value in that array, but I input the double quotation “” sign (empty string) to find the empty value in that range. This sign signifies the empty value.