Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: declaring a multidemensional array

    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
    Last edited by mikezx10; 05-18-09 at 14:31.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    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

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...
    Last edited by Colin Legg; 05-18-09 at 17:07.

  5. #5
    Join Date
    Oct 2003
    Posts
    233
    Now thats what im talkin about!


    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •