VBA Array Slices: How to Extract and Copy Array Parts Safely

VBA & macros · Updated November 4, 2026

VBA has no built-in array-slice syntax: to take a range of elements, copy them into a new array with a loop. For worksheet data, you can also read the desired cells into an array directly; the right method depends on whether your source is an in-memory array or a range.

What an array slice means in VBA

An array slice is a selection of part of an array, usually a consecutive group of elements. For example, if an array contains six names at indexes 0 through 5, a slice from index 1 through index 3 contains the second, third, and fourth names. In languages such as Python, slice notation expresses that selection directly. VBA does not support comparable syntax such as values(1:3).

In VBA, selecting elements and creating a smaller array are separate tasks. A loop reads each requested source element and assigns it to a destination array. This produces a copy: changing the copied values does not change the corresponding values in the original array. If you only need to process a few values, you may not need a new array at all; a loop over the selected indexes can be simpler and use less memory.

Keep the terms “index” and “position” distinct. An array’s index is its actual VBA subscript, which may start at zero, one, or another specified lower bound. Its position is where an element appears in a human-oriented sequence. A request for the third element does not necessarily mean index 3.

Copy a one-dimensional array slice

This helper accepts a one-dimensional array held in a Variant, checks the requested bounds, and returns a zero-based Variant array containing the selected values. It assumes the source is an initialized, one-dimensional array; IsArray confirms that a value is an array, but does not establish that it has only one dimension.

Public Function SliceVariant1D(ByVal source As Variant, _
                               ByVal firstIndex As Long, _
                               ByVal lastIndex As Long) As Variant
    Dim sourceLower As Long
    Dim sourceUpper As Long
    Dim i As Long
    Dim result() As Variant

    ' This helper expects an initialized one-dimensional array.
    If Not IsArray(source) Then
        Err.Raise vbObjectError + 1000, "SliceVariant1D", "Source must be an array."
    End If

    sourceLower = LBound(source)
    sourceUpper = UBound(source)

    ' Reject reversed or out-of-range requests.
    If firstIndex > lastIndex Then
        Err.Raise vbObjectError + 1001, "SliceVariant1D", "First index exceeds last index."
    End If
    If firstIndex < sourceLower Or lastIndex > sourceUpper Then
        Err.Raise vbObjectError + 1002, "SliceVariant1D", "Requested indexes are outside the array."
    End If

    ReDim result(0 To lastIndex - firstIndex)
    For i = firstIndex To lastIndex
        result(i - firstIndex) = source(i)
    Next i

    SliceVariant1D = result
End Function

For instance, this call returns three elements from an array created by VBA’s Array function:

Sub ShowSlice()
    Dim values As Variant
    Dim part As Variant
    Dim i As Long

    ' Array creates a zero-based array in this example.
    values = Array("red", "green", "blue", "gold", "violet")
    part = SliceVariant1D(values, 1, 3)

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

The printed values are green, blue, and gold. The returned array has bounds 0 through 2, even though the original indexes were 1 through 3. That is deliberate: the helper uses the source indexes to find values, then numbers the new array from zero. If you need the result to retain the same lower bound as the source, allocate it with ReDim result(firstIndex To lastIndex) and assign to result(i) instead.

Check array bounds before slicing

VBA arrays do not all start at zero. Array normally creates a zero-based array, while an array declared with a range such as Dim scores(1 To 10) As Long starts at one. A module-level Option Base 1 can affect the default lower bound for arrays declared without an explicit lower bound, but it does not change arrays whose bounds are specified explicitly. Always use LBound and UBound when code must work with varying bounds.

For a one-dimensional array, LBound(values) returns its lowest valid index and UBound(values) returns its highest. For a multidimensional array, provide the dimension number, as in LBound(data, 2). A requested first index below the lower bound, last index above the upper bound, or first index greater than last index should be handled as an error or an explicit empty result. Otherwise, the loop can fail with a subscript-out-of-range error.

There is an additional edge case: calling LBound or UBound on an uninitialized dynamic array raises an error. A reusable production routine may need a deliberate policy for that case, such as returning an empty result or raising a descriptive error. Avoid treating an empty or uninitialized array as if it necessarily had bounds from zero to minus one.

Extract a row or column from a two-dimensional array

Excel range data is commonly stored in a two-dimensional array, with one dimension for rows and another for columns. A slice of one row or one column can be copied into a one-dimensional result by fixing one index and looping over the other. For example, after reading cells A1:C5 into a two-dimensional Variant array, the following code copies the values corresponding to B2:B4:

Sub CopyRangeColumnValues()
    Dim data As Variant
    Dim result() As Variant
    Dim rowIndex As Long

    ' Read a 5-row by 3-column range into a two-dimensional array.
    data = ThisWorkbook.Worksheets("Sheet1").Range("A1:C5").Value2

    ' Copy rows 2 through 4 of the range's second column.
    ReDim result(0 To 2)
    For rowIndex = 2 To 4
        result(rowIndex - 2) = data(rowIndex, 2)
    Next rowIndex
End Sub

Here, the array’s first dimension represents the range’s rows and its second dimension represents the columns. Because the range begins at A1, its first row and column appear at array indexes 1 and 1. If the range starts elsewhere on the worksheet, the array indexes still refer to positions within the returned range, not worksheet row and column numbers. Use LBound and UBound for each dimension if the code should handle arrays with varying bounds.

The example assumes a range with more than one cell. A one-cell range’s Value2 is a scalar value rather than a two-dimensional array, so code that accepts arbitrary range sizes should account for that difference before indexing it. Also check that your requested row and column indexes are within the bounds of the appropriate dimensions.

Use a worksheet range when the source is cells

If the information is already on a worksheet, a VBA array slice may be unnecessary. Read only the cells you need rather than loading a larger range and copying part of its array. For example, assigning Range("B2:B4").Value2 to a Variant reads that three-cell range. This can make the intent clearer and avoids writing a separate copying loop.

Reading a larger block into a Variant array is useful when a macro must examine or transform many cells in memory and write results back efficiently. The usual pattern is to read a contiguous range once, loop through the array, then assign an output array to a destination range of matching dimensions. The array is then a working copy of the cell values; it is not a live view that updates automatically when worksheet cells change.

Use Value2 when you want the underlying cell values without VBA’s Currency and Date conversions. It does not preserve cell formatting: number formats, colors, and borders are separate worksheet properties. If your task is to move or format cells rather than process their values, work with Range objects instead of copying their values into an array.

Why ReDim Preserve is not a slice operator

ReDim Preserve resizes a dynamic array while keeping existing elements; it does not select an arbitrary range of values or create a slice by itself. A common approach is to allocate a destination array of the required size and copy the desired elements into it. That is more direct than attempting to resize the source and discard unwanted values.

For a multidimensional array, VBA limits what can be changed with ReDim Preserve: you can resize only the last dimension, and you cannot change the number of dimensions. For example, a two-dimensional array can have its final dimension resized while preserving data, but not its first dimension. This constraint often makes a separate output array the clearer option when extracting rows or columns.

Resizing a dynamic array without Preserve discards its existing contents. With Preserve, existing values are retained only where the old and new bounds overlap. Plan the allocation and copy together, and do not assume resizing changes the array into a view of part of its former contents.

Choose a method for the job

The most suitable method depends on what the data represents and what you intend to do with it:

  • Use a loop and a new array when you need a reusable copy of part of an in-memory array.
  • Loop over selected indexes without copying when you only need to calculate, inspect, or print those elements once.
  • Read a smaller range when the desired values already occupy a contiguous block of worksheet cells.
  • Use a range object when the task concerns cell formatting, formulas, or moving worksheet cells rather than just their values.
  • Use string functions such as Split when the source is delimited text, not an existing array. Split produces an array of text segments; selecting some of those segments still requires indexing or copying.

There is no general-purpose native VBA function that accepts an arbitrary array and returns a positional slice of it. A custom helper makes sense when the same operation appears repeatedly, but for a single short task a simple loop may be easier to read and maintain.

Common problems and safer macro habits

One frequent problem is confusing an array’s position with its subscript. If an array starts at 1, selecting indexes 2 through 4 means three values, just as selecting indexes 0 through 2 does in a zero-based array. Another is assuming that every array returned from a worksheet or created in VBA has identical bounds. Inspect bounds rather than hard-coding them unless the code explicitly controls how the array is created.

Also consider the element type. The sample helper returns a Variant array, which is convenient for mixed values and worksheet data, but it may not be the right return type for a performance-sensitive routine that works exclusively with a specific type such as Long. A typed array needs a correspondingly typed routine or a different design; do not assume every typed VBA array can be passed to a Variant-array helper without type or argument-passing complications.

To open the VBA editor in Excel for Microsoft 365 on Windows, press Alt+F11 or choose Developer > Visual Basic. On a Mac, Option+F11 is commonly used, though the keyboard may require the Fn key; the Visual Basic Editor is also available from Excel’s Tools > Macro menu. Save macro-enabled workbooks as .xlsm when they contain VBA. Before running a macro that changes workbook data, save a copy: macro actions cannot be undone with Ctrl+Z. On Mac, use Command+Z for ordinary undo, but it does not make VBA macro changes undoable.

Frequently Asked Questions

Does VBA have array slice syntax?

No. VBA does not have a general syntax such as array(start:end) for selecting a range of array elements. Copy the requested values in a loop, process the indexes directly, or read only the needed worksheet range.

How do I copy part of an array into a new array?

Check the source bounds, allocate a destination array with the required number of elements, and loop from the first requested source index through the last. Assign each source element to a destination index, adjusting the destination index if its lower bound differs.

Can I slice a two-dimensional VBA array?

Yes, by copying the desired row, column, or rectangular section element by element. Use the correct dimension order and check the lower and upper bounds of both dimensions. VBA does not provide a single general-purpose slice operator for multidimensional arrays.

Does ReDim Preserve remove unwanted elements?

No. It changes the size of a dynamic array while retaining overlapping values; it does not select an arbitrary subset. For multidimensional arrays, only the last dimension can be resized with Preserve. Use a separate destination array for a clear, general extraction.

Are Excel range values a one-dimensional array?

A multi-cell range’s Value or Value2 is generally returned as a two-dimensional Variant array, including for a single row or column. A one-cell range instead returns a scalar. Code that handles both cases must account for that difference.

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.