Create a row with subtotals above data to be added with a Loop through an array in VBA <p>I need to programmatically format an excel table creating two different subtotals base on changes of values in two specified column (the first and second of the spreadsheet). </p> <p>I managed to code the routine for the first subtotals but I’m now stuck trying to figure out the second part of the code.</p> <p>In the second routine I need to create the subtotal row above the data that will be added. I couldn’t manage to insert the calculation in the right row and there is something wrong when I do the loop through the columns included in the array. </p> <p>Anyone that can see what I’m doing wrong?</p> <p>Thank you</p> <pre><code>Public Sub SubtotalTable() Dim RowNumber As Long Dim RangePointer As Range Dim RangeTopRow As Range 'Pointing the column to check Set RangePointer = ActiveSheet.Range("B1") 'Assigning the first row to a range Set RangeTopRow = Rows(2) 'Assigning to long a variable the number of row from which begin checking RowNumber = 3 Do If RangePointer.Offset(RowNumber).Value &lt;&gt; RangePointer.Offset(RowNumber - 1).Value Then Set RangeTopRow = Rows(RowNumber) 'Call the function to insert the row Call InsertRowTotalsAbove(RangePointer.Offset(RowNumber), RangeTopRow, RowNumber) Else RowNumber = RowNumber + 1 End If RowNumber = RowNumber + 1 Loop End Sub Public Function InsertRowTotalsAbove(RangePointer As Range, lastRow As Range, RowNumber As Long) Dim ArrayColumns() As Variant Dim ArrayElement As Variant Dim newRange As Range 'Assigning number of columns to an array ArrayColumns = Array("D", "E", "F", "G") Do If RangePointer.Offset(RowNumber).Value = RangePointer.Offset(RowNumber - 1).Value Then RowNumber = RowNumber - 1 Else ActiveSheet.Cells(RowNumber + 1, 2).Select Set newRange = Range(ActiveCell, ActiveCell.Offset(0, 0)) Rows(newRange.Offset(RowNumber + 1).Row).Insert shift:=xlDown newRange.Offset(RowNumber + 1, 0).Value = "Totale" &amp; " " &amp; newRange.Offset(RowNumber + 2, 0) For Each ArrayElement In ArrayColumns newRange.Cells(RowNumber, ArrayElement).Value = Application.WorksheetFunction.Sum(Range(lastRow.Cells(-1, ArrayElement).Address &amp; ":" &amp; RangePointer.Cells(0, ArrayElement).Address)) Next ArrayElement End If Loop End Function </code></pre>