Can you create an array from an array in VBA?

Question

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

I should have clarified that. Ultimately I want to save the arrays as a CSV. However I haven't seen a clean way to do that. Instead I want to "print" to a range and than create a CSV from that sheet. @FaneDuru Print as in put the array into a range. — Manuel
– Manuel, Commented Dec 1, 2021 at 19:29
Yes that is correct. it is intended. I am trying total the cost for duplicate items (meaning matching rep). @CDP1802 — Manuel
– Manuel, Commented Dec 1, 2021 at 19:50
OK but it would be more usual to total the total costs column 7 G — CDP1802
– CDP1802, Commented Dec 1, 2021 at 20:05

CDP1802 · Accepted Answer · 2021-12-02 10:38:07Z

3

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.

update - include headers

Sub mergeCategoryValues2()

    Dim arr2 As Variant, arOut As Variant
    Dim rowcount As Long, colcount As Long
    Dim i As Long, k As Long, v
    
    arr2 = ActiveSheet.ListObjects("APPLE").Range
    rowcount = UBound(arr2, 1)
    colcount = UBound(arr2, 2)
    
    ReDim arOut(1 To rowcount, 1 To 3)
    
    For i = 1 To rowcount
        If arr2(i, 3) = v Then
            arOut(k, 3) = arOut(k, 3) + arr2(i, 6)
        Else
            k = k + 1
            v = arr2(i, 3) ' compare with following rows
            arOut(k, 1) = arr2(i, 2)
            arOut(k, 2) = arr2(i, 3)
            arOut(k, 3) = arr2(i, 6)
        End If
    Next i
    
    Sheet2.Range("A1").Resize(k, 3).Value2 = arOut
    MsgBox "OK"
End Sub

edited Dec 2, 2021 at 10:38

answered Dec 1, 2021 at 21:04

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Manuel Over a year ago

That works incredibly well thank you! Are you able to explain a little more how it works with the if statement?

CDP1802 Over a year ago

@Manuel The If is the same as your code, if the col C is the same as the previous col C then add to the existing row in the output array. If it isn't (else) then move to a new row with k=k+1 and create a new entry.

Manuel Over a year ago

How does this line work Sheet2.Range("A2").Resize(k, 3).Value2 = arOut I have never seen resize before.

CDP1802 Over a year ago

@Manuel If k was 4 then Range("A2").Resize(k, 3) would be the same as Range("A2:C5"), it just expands the range by 4 rows, 3 columns.

CDP1802 Over a year ago

@Manuel Jut so the result came out in same same order as the data. You normally scan upwards when deleting rows

|

Collectives™ on Stack Overflow

Can you create an array from an array in VBA?

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related