declaring a multidemensional array

mikezx10 · #1 (**permalink**) 05-18-09, 13:25

I need a 3d array, when it is declared i dont know how many rows it will have, how can i do that ?

I would think dim myArray(,) but that doesnt work

hmm how do i asign values?

i have a range with :
a b c
9 100
3 10
4 30
8 62

would i do this?

Dim numJEs As Integer
numJEs = ActiveSheet.UsedRange.rows.Count - 1

Dim JEs(numJEs, 3)
Dim i As Integer

For i = 0 To numJEs - 1
JEs(i, i, i) = Range("A" + i)
JEs(i, i) = Range("B" + i)
JEs(i, i, i) = Range("C" + i)
Next

Colin Legg · #2 (**permalink**) 05-18-09, 13:47

Hi Mike,

Your worksheet layout is not clear on your post. Where does the third dimension come from?

To use dynamic arrays in VBA, have a look at the ReDim statement in your Excel VBA helpfile.

Hope that helps...

mikezx10 · #3 (**permalink**) 05-18-09, 14:15

sorry about that,,, I got what i needed here.. If anyone can make a recomendation i appreciate it

Sub a()
Dim numJEs As Integer
numJEs = ActiveSheet.UsedRange.rows.Count - 1

ReDim JEs(numJEs - 1, 2)

Dim i As Integer

For i = 0 To numJEs - 1 ' rows start at 2
JEs(i, 0) = Cells(i + 2, 1) 'Row ColA
JEs(i, 1) = Cells(i + 2, 2) 'Row ColB
JEs(i, 2) = Cells(i + 2, 3) 'Row ColC
Next

End Sub

Colin Legg · #4 (**permalink**) 05-18-09, 16:02

Hi Mike,

No problem.

I do have a recommendation. How about this instead? No looping and a more reliable way of determining the last row:

Code:

Sub a()

    Dim vMatrix As Variant
    Dim rngLastRow As Range
    
    'a handle on the range we are working with
    With ActiveSheet.Range("A:C")
    
        'get the LastRow
        Set rngLastRow = .Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious)
        
        'if the range contains data then populate our variant array
        If Not rngLastRow Is Nothing Then
            vMatrix = .Resize(rngLastRow.Row - .Row + 1, .Columns.Count)
        End If
        
    End With
    
End Sub

Hope that helps...

mikezx10 · #5 (**permalink**) 05-19-09, 15:45

Now thats what im talkin about!

Thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech