The Problem (Q-score 18, ranked #24th of 303 in the Excel VBA archive)
The scenario as originally posted in 2008
What function can I use in Excel VBA to slice an array?
Why community consensus is tight on this one
Across 303 Excel VBA entries in the archive, the accepted answer here holds elite answer (top 10 %%) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — elite answer (top 10 %%) (+31)
54-line Excel VBA pattern (copy-ready)
Application.WorksheetFunction.Index(array, row, column)
If you specify a zero value for row or column, then you’ll get the entire column or row that is specified.
Example:
Application.WorksheetFunction.Index(array, 0, 3)
This will give you the entire 3rd column.
If you specify both row and column as non-zero, then you’ll get only the specific element.
There is no easy way to get a smaller slice than a complete row or column.
Limitation: There is a limit to the array size that WorksheetFunction.Index can handle if you’re using a newer version of Excel. If array has more than 65,536 rows or 65,536 columns, then it throws a “Type mismatch” error. If this is an issue for you, then see this more complicated answer which is not subject to the same limitation.
Here’s the function I wrote to do all my 1D and 2D slicing:
Public Function GetArraySlice2D(Sarray As Variant, Stype As String, Sindex As Integer, Sstart As Integer, Sfinish As Integer) As Variant
' this function returns a slice of an array, Stype is either row or column
' Sstart is beginning of slice, Sfinish is end of slice (Sfinish = 0 means entire
' row or column is taken), Sindex is the row or column to be sliced
' (NOTE: 1 is always the first row or first column)
' an Sindex value of 0 means that the array is one dimensional 3/20/09 ljr
Dim vtemp() As Variant
Dim i As Integer
On Err GoTo ErrHandler
Select Case Sindex
Case 0
If Sfinish - Sstart = UBound(Sarray) - LBound(Sarray) Then
vtemp = Sarray
Else
ReDim vtemp(1 To Sfinish - Sstart + 1)
For i = 1 To Sfinish - Sstart + 1
vtemp(i) = Sarray(i + Sstart - 1)
Next i
End If
Case Else
Select Case Stype
Case "row"
If Sfinish = 0 Or (Sstart = LBound(Sarray, 2) And Sfinish = UBound(Sarray, 2)) Then
vtemp = Application.WorksheetFunction.Index(Sarray, Sindex, 0)
Else
ReDim vtemp(1 To Sfinish - Sstart + 1)
For i = 1 To Sfinish - Sstart + 1
vtemp(i) = Sarray(Sindex, i + Sstart - 1)
Next i
End If
Case "column"
If Sfinish = 0 Or (Sstart = LBound(Sarray, 1) And Sfinish = UBound(Sarray, 1)) Then
vtemp = Application.WorksheetFunction.Index(Sarray, 0, Sindex)
Else
ReDim vtemp(1 To Sfinish - Sstart + 1)
For i = 1 To Sfinish - Sstart + 1
vtemp(i) = Sarray(i + Sstart - 1, Sindex)
Next i
End If
End Select
End Select
GetArraySlice2D = vtemp
Exit Function
ErrHandler:
Dim M As Integer
M = MsgBox("Bad Array Input", vbOKOnly, "GetArraySlice2D")
End Function
Error-handling details to lift with the snippet
This answer wires error flow through MsgBox / Err.Description. Keep that intact: stripping it to “make it cleaner” removes the signal you’ll need when the macro fails silently on a user machine.
Loop-performance notes specific to this pattern
The loop in the answer iterates in process. On a 2026 Office build, setting Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual around a loop of this size typically cuts runtime by 40–70%. Re-enable both in the Exit handler.
When to Use It — vintage (14+ years old, pre-2013)
Ranked #24th in its category — specialized fit
This pattern sits in the 91% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Excel VBA archive for a higher-consensus alternative.
What changed between 2008 and 2026
The answer is 18 years old. The Excel VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.