Can you create an array from an array in VBA?-CodePudding

Edit: By print array I mean put the array onto a range in a sheet :)

I am using the following code on a table in excel with VBA. This combines rows with matching sales rep. Below is the source table. This is loaded into an array.

After running the below code the rows are combined and I null out the rows that were combined. My challenge is to print certain columns and only print the non null rows. To accomplish this I was trying to loop through the array and create another array with just the non null rows.

Sub mergeCategoryValues2()
Dim arr2 As Variant
Dim rowcount As Long
Dim i As Variant
Dim colcount As Long

arr2 = ActiveSheet.ListObjects("APPLE").Range
rowcount = UBound(arr2, 1)
colcount = UBound(arr2, 2)
For i = rowcount To 2 Step -1


               If arr2(i, 3) = arr2(i - 1, 3) Then
arr2(i - 1, 6) = arr2(i - 1, 6)   arr2(i, 6)

For k = 1 To colcount
               arr2(i, k) = Null 'this loop is probably not required i can probably just use the first column
              
               Next k
               End If
Next i

End Sub

Ultimately I wanted to print just the non null rows and just Columns 3,2,and 6. The best way I thought was to create an array with non null rows

CodePudding user response：

Create an array for the results with the same number of rows as the data array. Scan down the data rows and at each change of value in column C increment a row counter for the results array . Dump the used part of the results using resize.


Option Explicit
Sub mergeCategoryValues2()

    Dim arr2 As Variant, arOut As Variant
    Dim rowcount As Long, colcount As Long
    Dim i As Long, k As Long
    
    arr2 = ActiveSheet.ListObjects("APPLE").Range
    rowcount = UBound(arr2, 1)
    colcount = UBound(arr2, 2)
    
    ReDim arOut(1 To rowcount, 1 To 3)
    
    For i = 2 To rowcount
        If arr2(i, 3) = arr2(i - 1, 3) Then
           arOut(k, 3) = arOut(k, 3)   arr2(i, 6)
        Else
           k = k   1
           arOut(k, 1) = arr2(i, 2)
           arOut(k, 2) = arr2(i, 3)
           arOut(k, 3) = arr2(i, 6)
        End If
    Next i
    
    Sheet2.Range("A2").Resize(k, 3).Value2 = arOut
    MsgBox "OK"
End Sub