Excel – Get length of array?

In Excel, the “length” of an array typically refers to the number of elements in the array. Here’s how you can determine the length of an array depending on the context:

1. Length of a Range (Array of Cells)

If you have an array of cells (e.g., A1:A10), you can use the ROWS and COLUMNS functions to determine its dimensions:

Count the Number of Rows in the Array

=ROWS(A1:A10)

• This will return 10 for the range A1:A10.

Count the Number of Columns in the Array

=COLUMNS(A1:C1)

• This will return 3 for the range A1:C1.

Combine Rows and Columns for Total Count

To get the total number of elements in a 2D array (e.g., A1:C10):

=ROWS(A1:C10) * COLUMNS(A1:C10)

• This will return 30 for the range A1:C10.

2. Length of a Dynamic Array (Excel 365/2021)

If you’re working with a dynamic array formula (e.g., SORT, UNIQUE, etc.), use the same ROWS or COLUMNS functions on the spilled range:

=ROWS(B1#)

• Here, B1# references the entire spilled range starting at B1.

3. Length of a String Array

If you have a text array like {10,20,30,40}, use the following techniques:

Count Number of Elements

Use the LEN and SUBSTITUTE functions:

=LEN(TEXTJOIN(",",,10,20,30,40)) - LEN(SUBSTITUTE(TEXTJOIN(",",,10,20,30,40),",","")) + 1

• This counts the number of commas and adds 1 to get the number of elements.
• Replace {10,20,30,40} with your own array.

4. Count Non-Blank Elements in a Range

To count the number of non-blank elements in a range:

=COUNTA(A1:A10)

• This ignores blank cells and counts only the non-blank ones.

5. Using VBA for Array Length

If you’re working with arrays in VBA (Visual Basic for Applications), you can use the UBound and LBound functions to find the length of an array.

Example VBA Code:

Sub GetArrayLength()
    Dim arr() As Variant
    arr = Array(10, 20, 30, 40)
    MsgBox "Length of the array is: " & UBound(arr) - LBound(arr) + 1
End Sub

• UBound(arr) gives the upper bound, and LBound(arr) gives the lower bound. Subtracting them and adding 1 gives the total number of elements.

6. Array Formulas

For older Excel versions (pre-dynamic arrays), use array formulas (entered with Ctrl+Shift+Enter):

=SUM(IF(A1:A10<>"",1,0))

• This counts non-blank cells in the range A1:A10.

Conclusion

• Use ROWS and COLUMNS for ranges.
• Use COUNTA to count non-blank cells.
• For dynamic arrays, reference the spilled range using #.
• In VBA, use UBound and LBound.

Let me know if you need further help with a specific case!