how to fill an array with column values

user_14669692 0
Viewed 43

I got the following data, I need to change the name that appears on the subtotals rows, by adding to them the correspondent category, that appears in another column.

The range of the data is dynamic but is defined, so I got the start and the end of the range.

I was thinking to add the non-empty cells of DimCol2 in an array and after replacing the word subtotal with 'subtotal " & array(i). How can this be done? enter image description here

vba excel
1 Answers

Please, try the next code:

Sub SubtotalValues()
   Dim sh As Worksheet, lastR As Long, arr, arrDC, i As Long, dimCol2 As String
   
   Set sh = ActiveSheet 'use here the sheet you need
   lastR = sh.Range("A" & sh.rows.count).End(xlUp).row - 1 'last usefull row (excepting the Total)
   
   arr = sh.Range("A3:C" & lastR).Value2 'place the range in an array for faster iteration
   ReDim arrDC(1 To UBound(arr), 1 To 1) 'reDim the array to receive the processed result
   
   For i = 1 To UBound(arr)
       If arr(i, 1) <> "" Then dimCol2 = arr(i, 1)
       arrDC(i, 1) = arr(i, 2)
       If LCase(arr(i, 2)) = "subtotal" Then
            arrDC(i, 1) = arr(i, 3) & " " & dimCol2
       End If
   Next i
   'drop the processed array content:
   sh.Range("D3").Resize(UBound(arrDC), 1).Value2 = arrDC
   sh.range("D2").value = "DimCol"
End Sub

But it is good to learn that asking a question does not mean only a requirement, showing as a theme... You should prove that did some research in order to solve it by yourself! If not a piece of code (even not returning what you want) anything else to prove your involvement in solving the problem.

user_22333080
Related