Finding the length of elements in a 2D array

[mobucl]

Hi all,

How can i get the length of a 1D element inside a 2D array. For example. I have the following data in an excel sheet:



1
10
20


2
11
21


3
12
22


4
13
23


5
14
24


6
15
25


7
16
26


8
17
27


9
18
28


10
19
29


11
20
30


12

31


13

32


14

33



and the following code:




Sub test()Dim var As Variant


var = Range(Cells(1, 1), Cells(14, 3))


For i = 1 To 3
Debug.Print var(1, i)
Next i


For ii = 1 To 14
Debug.Print var(ii, 1)
Next ii


End Sub




So i can access all values in my array using var(row,column). But how can i find the length of each column (for example length var(1) = 14, var(2) = 11, var(3) = 14.

I tried using Ubound but this only returns the total length and width of the array

Ubound(var,1) = 14
Unound(var,2) = 3
Ubount(var,3) - doesnt work as its a 2D array.

Can the length of each element in the array be determined directly or do i need to copy the data out of the array to check?

Thanks in advance for any help

Matt

[mdmackillop]

Something like this
Option Explicit
Sub Test()
Dim arr As Variant
Dim x As Variant
Dim i&, j&, tot&


arr = Cells(1, 1).CurrentRegion
MsgBox "overall length " & UBound(arr, 1)
MsgBox "overall width " & UBound(arr, 2)
For i = 1 To UBound(arr, 2)
x = Application.Index(arr, 0, i)
For j = 1 To UBound(x)
If x(j, 1) <> "" Then tot = tot + 1
Next j
MsgBox "Column " & i & " = " & tot
tot = 0
Next
End Sub

[Paul_Hossler]

I'd just 'back up' the column looking for the first non-empty element




Option Explicit

Sub test()
Dim i As Long, N As Long
Dim A As Variant

A = ActiveSheet.Range("A1").CurrentRegion

MsgBox UBound(A, 1)
MsgBox UBound(A, 2)

For i = UBound(A, 1) To LBound(A, 1) Step -1
If Not IsEmpty(A(i, 2)) Then
N = I
Exit For
End If
Next I

MsgBox N
End Sub

[mobucl]

Thanks mdmackillop and Paul! Both these ways should work with what im wanting to do.

Matt