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

An array is essentially a group of things. The elements may take the form of text or numbers and may be placed in a single row, a single column, or numerous rows and columns. If you are looking for some easy ways to check if an array is empty with VBA, I am here to give you 3 very simple ways to do so. I hope it will be helpful for you.

For more explanation, I have used a dataset on gangsters’ secret names in the Serial No, Gangster, and Pseudonym columns.

Set an Array with VBA in Excel

First of all, in order to testify whether an array is empty or not, we have to assign an array with VBA. Then, in the following process, we can go through the checking process to verify that array. We need to follow the following process to set an array with VBA.

Steps:

• Firstly, go to the Developer tab.
• Then, click on Visual Basic from the ribbon.

• Next, click on the Insert tab from the Microsoft Visual Basic for Applications window.
• Select Module from the available options.

• Now, write 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

• To have the array output, go to the Immediate Window option from the View tab.

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

• Now, click on the Run or F5 button to execute the code.

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

How to Check If Array Is Empty in Excel: 3 VBA Variants

1. Applying VBA with JOIN Function

VBA is the most innovative way to perform anything in Excel. To check if the array is empty, I am going to use the JOIN function with the VBA code.

Steps:

• Open a VBA module following the above-mentioned process.
• Now, write down 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

• Next, run the code by clicking on the Run or F5 button.

As our array has values, we can see in the Immediate window that 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.

Read More: How to Find Lookup Value in Array in Excel VBA

2. VBA ISEMPTY Function to Check If Array Is Empty

Alternatively, we can use another VBA combined with the ISEMPTY function to check if an array is empty or not. That process is mentioned below.

Steps:

• Follow the above-mentioned process to open a VBA module.
• Now, input the following VBA code to fulfill the purpose.

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

• Next, run the code by clicking on the Run or F5 button.

Thus, we can verify the array.

Read More: Excel VBA: Determine Number of Elements in Array

3. Manual Procedure to Check If Array Is Empty

There is also another process where we can check the array is empty by manual process. For this, we have opened the module following the above process.

Steps:

• Write the code mentioned below:

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

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

• Finally, click on the Run or F5 button.

Thus, we can verify the array.

Download Practice Workbook

Conclusion

That’s all for today. I have tried to explain 3 VBA codes to check if an array is empty. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.

Related Articles

• Excel VBA to Populate Array with Cell Values
• VBA Array Size Limit in Excel
• VBA to Get Array Dimensions in Excel