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.

VBA Check if Array is Empty


Download Practice Workbook


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.

Set an Array with VBA

  • 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

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 have applied For loop in the range D5:D14 to print the values in that range.

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

Set an Array with VBA in Excel

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.

Set an Array with VBA in Excel

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


3 VBA Variants to Check If Array Is Empty in Excel

1. Applying VBA with JOIN Function

VBA is the most innovative way to perform anything in Excel. In order 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

Applying VBA with JOIN Function

Code Explanation

Here, I have used CheckEmptyArray() as Sub_Procedure and Dim to declare variables. I have declared MyArray() as Variant which can be defined with any kind of value and G_sters as string value. Afterward, I have applied 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 to return the number of characters of the range. If the number of characters is greater than the number of rows, we can say that the array is not empty. If it gets equal to each other, the array is empty.

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

Applying VBA with JOIN Function

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 Declare Array in Excel VBA (3 Easy Ways)


Similar Readings


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

VBA ISEMPTY Function to Check If Array Is Empty

Code Explanation

If you look closer, you will be able to 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 there is any empty value found, the value of that variable 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.

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

Thus, we can have verified the array.

Read More: How to Use Arrays Instead of Ranges in Excel VBA


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

Manual Procedure to Check If Array Is Empty

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.

Read More: How to Convert Range to Array in Excel VBA (3 Ways)


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

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo