Saving Multiple excel Ranges into a 2 dimensional array - not all at the same

Question

Im writing an excel macro, that essentially analyzes a range of information (one row specifically), and if there are triplicates (3 or more of the same name in a row), it sets the range to red so it stands out. Now what i want to do is every time the program finds a triplicate range, it saves that range (3 rows by 8 columns) to an array. I understand how to save a single range into an array, but how do i add the next range that my program finds (and then the next range, thereafter). After the program runs and finds all triplicate ranges, I want it to take that 2-d array and paste all of the found data into an empty sheet.

    For k = 1 To LastRow - 1

                 '   If (k + 1 <= LastRow) Then

                    If (FunctionArray(k + 1) = FunctionArray(k)) Then
                        count = count + 1

                    ElseIf (count >= 3 And FunctionArray(k + 1) <> FunctionArray(k)) Then
                        StartPoint = k - (count - 2)
                        Range(Cells(StartPoint, 1), Cells(k + 1, 11)).Select
                        With Selection
                            .Font.Bold = True
                            .Font.Color = -16776961
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeBottom).LineStyle = xlContinuous
                            .Borders(xlEdgeTop).LineStyle = xlContinuous
                        End With



                        count = 1

                    ElseIf (count = 2 And FunctionArray(k + 1) <> FunctionArray(k)) Then
                        count = 1
                    End If


                Next k

If you look in the second IF statement, i already successfully wrote out the logic to find the desired range. i just need to save that information i find in a 2-d array and then continue to add the found ranges thereafter. Thank you for any help.

FunctionArray is simply a single column of information. So in my spreadsheet, column A is filled with several so-called 'IDs'. If i write FunctionArray(1), it pulls the ID from cell A2 (it has a zero index). FunctionArray(3) refers to the ID in column A4 and so on. FunctionArray is only used to check if three IDs in a row are the same. Once it finds that triplicate, it marks the range (bold), then the program continues to look for another triplicate. — Jacob Culleny, Jun 19 '15 at 14:49
You can create an array of arrays. Why don't you just paste the info on the new sheet once it's found instead of storing it? If you want the array of arrays, here is one approach: http://stackoverflow.com/questions/30631811/filling-a-3d-array-with-2d-arrays-in-vba/30633093#30633093 — Byron Wall, Jun 19 '15 at 15:26
Its not a function, just a name to the array. Not sure why i named it that. — Jacob Culleny, Jun 19 '15 at 19:16

score 2 · Answer 1 · answered Jun 19 '15 at 17:51

If I read you right, you want to take a variable number of rectangular ranges and, ultimately, gather all of the values into a single 2-dimensional array that you can paste into a worksheet. One method is to create a collection of ranges, then feed them to a function which will gather them into a single array. The following code shows one possible function as well as a test sub to illustrate what it does. To test is -- put values into ranges "A1:C2" and "B4:C6" and then run the test sub:

Function compactify(ranges As Collection) As Variant
'assumes that ranges is a non-empty collection
'of rectangular ranges
    Dim i As Long, j As Long, m As Long, n As Long
    Dim block As Variant
    Dim r As Range, myRow As Range
    For Each r In ranges
        m = m + r.Rows.Count
        If r.Columns.Count > n Then n = r.Columns.Count
    Next r
    ReDim block(1 To m, 1 To n)
    For Each r In ranges
        For Each myRow In r.Rows
            i = i + 1
            For j = 1 To myRow.Columns.Count
                block(i, j) = myRow.Cells(1, j).Value
            Next j
        Next myRow
    Next r
    compactify = block
End Function

Sub test()
    Dim myRanges As New Collection
    myRanges.Add Range("A1:C2")
    myRanges.Add Range("B4:C6")
    Range("A10:C14").Value = compactify(myRanges)
End Sub

John, thank you so much for taking the time to help me out with this. I will test this function, and if i have any questions, i will be sure to ask you, as you clearly know what youre talking about. :) — Jacob Culleny, Jun 19 '15 at 19:14

Saving Multiple excel Ranges into a 2 dimensional array - not all at the same

1 Answers1

Linked