Excel VBA Array Trim: Remove Blank Items

VBA & macros · Updated November 21, 2026

If you mean trimming an array in Excel VBA, VBA has no built-in function that removes blank elements from an array. The usual solution is to loop through a one-dimensional array and build a smaller one containing the items you want to keep; Trim$ itself only removes spaces at the start and end of a string.

What “array trim” means in VBA

The phrase “array trim” can refer to two different tasks. You might want to remove leading and trailing spaces from every text value in an array, or you might want to remove elements that are empty or contain only spaces. Those tasks are related, but they are not the same: cleaning a string does not change the number of elements in an array.

For example, an array containing "East", " ", and "West" still has three elements after you trim the strings. The middle value becomes an empty string, but its array position remains. To get an array containing only East and West, you must create a new array and copy the values you want to retain.

There is no standard VBA function named TrimArray. You can write a custom function with that name, as in the example below, but it is your own procedure—not a built-in Excel or VBA function. If “tri” meant something else, such as transposing an array, that is a different operation.

Trim$ versus removing array elements

VBA’s Trim and Trim$ functions remove ordinary leading and trailing spaces from a string. The dollar-sign version returns a String rather than a Variant and is commonly used when the input is text. Neither function removes spaces in the middle of a string, deletes an array item, or changes an array’s bounds.

Consider the string " North Zone ". Applying Trim$ gives "North Zone"; the single space between the words remains. Applying it to a string made only of ordinary spaces gives an empty string. That makes it useful for deciding whether a text array element is visually blank, but a separate copy-and-compact step is still required.

Also distinguish a zero-length string from other values that may look blank in a worksheet. An empty cell read into VBA, an empty string returned by a formula, a string of spaces, a numeric zero, and a cell containing an error are different values. Whether to retain zeroes or errors depends on the job. The sample function below removes only strings that become empty after Trim$; it keeps numbers, dates, error values, and Null values.

Example: compact a one-dimensional array

Put this code in a standard module in the VBA editor. It expects an allocated, one-dimensional array of scalar values. The result uses zero-based bounds, regardless of the source array’s original lower bound. If every element is a blank string, the function returns Empty rather than a zero-length array.

Public Function TrimArray(ByVal source As Variant) As Variant
    Dim firstIndex As Long, lastIndex As Long
    Dim i As Long, kept As Long
    Dim item As Variant
    Dim result() As Variant

    ' This function expects an allocated, one-dimensional array.
    firstIndex = LBound(source)
    lastIndex = UBound(source)
    ReDim result(0 To lastIndex - firstIndex)

    For i = firstIndex To lastIndex
        item = source(i)
        If IsError(item) Or IsNull(item) Then
            kept = kept + 1
            result(kept - 1) = item
        ElseIf VarType(item) = vbString Then
            If Len(Trim$(CStr(item))) > 0 Then
                kept = kept + 1
                result(kept - 1) = item
            End If
        Else
            kept = kept + 1
            result(kept - 1) = item
        End If
    Next i

    If kept = 0 Then
        TrimArray = Empty
    Else
        ReDim Preserve result(0 To kept - 1)
        TrimArray = result
    End If
End Function

Public Sub ExampleTrimArray()
    Dim values As Variant
    Dim i As Long

    values = Array("North", "   ", "South", vbNullString, 0)
    values = TrimArray(values)

    If IsEmpty(values) Then Exit Sub
    For i = LBound(values) To UBound(values)
        Debug.Print values(i)
    Next i
End Sub

In this example, the printed values are North, South, and 0. The space-only string and the zero-length string are omitted, while numeric zero remains. Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. On Windows, open the VBA editor with Alt+F11; on a Mac, the usual editor shortcut is Option+F11, though some keyboards require the Fn key as well.

How the routine works

The function first reads the source array’s lower and upper bounds with LBound and UBound. It allocates a temporary result large enough to hold every source element, then visits each item once. When an item qualifies to be kept, it copies it into the next available result position and increments the count.

After the loop, ReDim Preserve shrinks the temporary array to the number of retained elements. In VBA, Preserve can resize only the final dimension of an array. That limitation is manageable here because the example works with a one-dimensional array. The function deliberately writes the output using indexes from zero, so code that uses the result should get its bounds with LBound and UBound rather than assuming it starts at one.

The test Len(Trim$(CStr(item))) > 0 is applied only to strings. This avoids treating numeric zero as blank and avoids trying to convert an error value to text. If your rule is instead to remove every item whose string representation is blank, or to remove zeroes too, change the conditions to match that rule explicitly. Be careful with implicit conversions: a value’s displayed appearance in a cell does not always reveal its underlying VBA type.

Using the function with worksheet data

A common surprise is that reading a range into a VBA Variant usually produces a two-dimensional array, even when the range is a single column. The one-dimensional function above cannot accept that array directly. For a single-column range, copy its values into a one-dimensional array first, then pass that array to TrimArray.

With source values in A2:A100 on a worksheet named Sheet1, this example writes retained values to column B beginning at B2. The function from the earlier code block must also be present in the workbook’s VBA project.

Public Sub CompactColumnToB()
    Dim ws As Worksheet
    Dim source2D As Variant
    Dim oneD() As Variant
    Dim cleaned As Variant
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    source2D = ws.Range("A2:A100").Value2
    ReDim oneD(0 To UBound(source2D, 1) - 1)

    For i = 1 To UBound(source2D, 1)
        oneD(i - 1) = source2D(i, 1)
    Next i

    cleaned = TrimArray(oneD)
    If IsEmpty(cleaned) Then Exit Sub

    For i = LBound(cleaned) To UBound(cleaned)
        ws.Cells(i + 2, "B").Value = cleaned(i)
    Next i
End Sub

This code reads the source range’s values rather than formatting, and it does not alter the original cells. It writes only the new results; if an earlier run left extra values farther down column B, those old values are not cleared automatically. Review the destination before running the macro, and save a copy first because macro changes are not reversible with Ctrl+Z. For a single-cell source range or a range with a different layout, adapt the conversion and bounds logic rather than assuming the same dimensions.

Two-dimensional arrays and other options

For a multi-column range, decide what “blank item” means before writing a compaction routine. You may want to remove entire rows where one key column is blank, remove rows where every column is blank, or remove blank cells individually. These produce different results. A two-dimensional array needs nested loops or row-based copying; it cannot be safely compacted by passing it to the one-dimensional function above.

For example, if each row is a record identified by a value in column A, the usual rule is to keep or discard the whole row based on that key column. Copying only nonblank cells into one long array would destroy the relationship between each record’s fields. When preserving rows, allocate a result with the same number of columns, count qualifying rows, and copy each retained row together.

Other approaches can be more convenient depending on where the data lives. Excel’s worksheet FILTER function can return matching rows in a worksheet when available, while a VBA loop is useful when a macro needs to manipulate values in memory. A VBA Collection can grow as items are appended, but turning it back into an array takes additional code. The best choice depends on whether you need a worksheet result, a reusable VBA array, or a row-preserving transformation.

Common problems and limitations

  • Unallocated arrays: Calling LBound or UBound on an uninitialized dynamic array can raise an error. The sample assumes the caller supplies an allocated array with at least one element.
  • Two-dimensional input: Range values are commonly two-dimensional, so convert a single column or write a routine designed for rows and columns.
  • Whitespace beyond ordinary spaces: Trim$ is not a universal whitespace normalizer. Tabs, line breaks, and nonbreaking spaces may require explicit handling, such as replacing known characters before testing the string.
  • Changing the original array: The function returns a new array. Assign the result back, as in values = TrimArray(values), if you want the variable to refer to the compacted result.
  • Lost positions: Compaction shifts later values toward the start. If the original indexes carry meaning, keep a parallel index or key array instead of discarding those positions.

Frequently Asked Questions

Does VBA have a built-in TrimArray function?
No. VBA includes Trim and Trim$ for strings, but array compaction requires a loop or a custom function. A procedure called TrimArray is user-written code.

How do I remove blank elements from an Excel VBA array?
Loop through the array, test each value using the rule that fits your data, and copy retained values into a new array. The example removes strings that contain only ordinary spaces while preserving numeric zero and other non-string values.

Does Trim$ remove spaces inside a value?
No. It removes leading and trailing ordinary spaces. For example, it changes " North Zone " to "North Zone", but does not remove the space between North and Zone.

Can I pass Range(“A1:A10”).Value directly to this function?
Not to the one-dimensional function shown here. A multi-cell range’s Value or Value2 is generally a two-dimensional array, including a one-column range. Convert that data to one dimension or write a function that handles two dimensions.

Why does the result start at index zero?
The function builds a new array with explicit zero-based bounds. The source may start at a different index, so use LBound and UBound to loop over the returned array instead of assuming its first index.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.