Exclude Empty Strings from Array

When creating programs using VBA, you often need to manipulate data within arrays. This article explains how to exclude empty strings from an array. We’ll walk through the process step-by-step using specific code examples.

Basic Approach to Excluding Empty Strings

Here’s a common approach to exclude empty strings from an array:

1. Loop through the original array and count elements that are not empty strings.
2. Create a new array based on the count of non-empty elements.
3. Copy non-empty elements to the new array.

Let’s look at these steps in detail.

Code Example: Excluding Empty Strings from an Array

Below is a code example implementing the above steps using VBA.

Public Sub RemoveEmptyStrings()
    Dim originalArray As Variant
    Dim cleanedArray As Variant
    Dim i As Long
    Dim count As Long

    '_Define_the_original_array
    originalArray = Array("Apple", "", "Banana", "", "Cherry", "Date", "")

    '_Count_non-empty_elements
    count = 0
    For i = LBound(originalArray) To UBound(originalArray)
        If originalArray(i) <> "" Then
            count = count + 1
        End If
    Next i

    '_Redefine_the_array_excluding_empty_strings
    ReDim cleanedArray(0 To count - 1)

    '_Copy_non-empty_elements_from_the_original_array
    count = 0
    For i = LBound(originalArray) To UBound(originalArray)
        If originalArray(i) <> "" Then
            cleanedArray(count) = originalArray(i)
            count = count + 1
        End If
    Next i

    '_Output_the_results
    For i = LBound(cleanedArray) To UBound(cleanedArray)
        Debug.Print cleanedArray(i)
    Next i
End Sub

Code Explanation

1. Defining the Original Array:

   • originalArray contains sample data, including empty strings.

2. Counting Non-Empty Elements:

   • The first For loop counts the number of non-empty elements. This count becomes the size of the new array.

3. Creating a New Array:

   • The ReDim statement is used to define a new array cleanedArray that excludes empty strings.

4. Copying Non-Empty Elements:

   • The second For loop copies non-empty elements to cleanedArray.

5. Outputting the Results:

   • Finally, the contents of cleanedArray are output using Debug.Print.

Summary

We’ve introduced a method to exclude empty strings from an array in VBA. The basic approach involves looping through the array and copying non-empty elements to a new array. Use this method in your actual projects to efficiently clean up array data.