Exclude Empty Strings from Array

VBA arrays often end up with empty-string ("") elements, for example from data read off a sheet or from the result of the Split function.

VBA has no built-in function for filtering array elements by a condition, so removing empty strings means writing the loop yourself.

This article builds a function that removes empty strings from an array and explains how to use it and what to watch out for.

The basic approach

1. Prepare an array as large as the original
2. Loop over the original and copy only the non-empty elements in order
3. Finally, shrink the array to the number of copied elements with ReDim Preserve

Code example: a function that removes empty strings

Public Function RemoveEmptyStrings(ByVal source As Variant) As Variant
    Dim result() As Variant
    Dim i As Long
    Dim count As Long

    ' If the array has no elements, return an empty array as-is
    If UBound(source) < LBound(source) Then
        RemoveEmptyStrings = Array()
        Exit Function
    End If

    ReDim result(0 To UBound(source) - LBound(source))

    For i = LBound(source) To UBound(source)
        If Len(source(i)) > 0 Then
            result(count) = source(i)
            count = count + 1
        End If
    Next i

    ' If every element was empty, return an empty array
    If count = 0 Then
        RemoveEmptyStrings = Array()
        Exit Function
    End If

    ReDim Preserve result(0 To count - 1)
    RemoveEmptyStrings = result
End Function

Here is an example of calling it.

Public Sub RemoveEmptyStringsSample()
    Dim originalArray As Variant
    Dim cleanedArray As Variant
    Dim i As Long

    originalArray = Array("Apple", "", "Banana", "", "Cherry", "Date", "")
    cleanedArray = RemoveEmptyStrings(originalArray)

    ' An empty result has UBound < LBound
    If UBound(cleanedArray) < LBound(cleanedArray) Then
        Debug.Print "No valid elements"
        Exit Sub
    End If

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

The Immediate window prints Apple, Banana, Cherry, and Date.

Key points in the code

• Test with Len(source(i)) > 0: This gives the same result as source(i) <> "", but does not raise an error if an element is Empty or Null (both are removed)
• Use LBound / UBound: The starting index of arrays made with the Array function depends on Option Base, and arrays obtained from Range.Value are always 1-based. Do not hard-code the starting index
• When every element is empty: ReDim Preserve result(0 To -1) raises an error, so when count = 0 the function returns Array(), an empty array
• Checking the return value: An empty array can be detected with UBound(array) < LBound(array)

Removing whitespace-only elements too

To also remove elements made up only of spaces, trim leading and trailing whitespace with Trim before testing.

If Len(Trim(source(i))) > 0 Then

To also exclude full-width spaces, combine this with something like Replace(source(i), "　", "").

Summary

• VBA has no built-in function to remove empty strings, so copy the elements into a new array with a loop
• Prepare an array of the original size, fill it, and shrink it once with ReDim Preserve at the end for efficiency
• Return Array() when every element is empty so that ReDim ... To -1 does not raise an error
• Use Trim when you also want to remove whitespace-only elements