# Subtotal dynamic range

• ### Question

• Hello,

The following subtotal code works great when there are 26 columns, ie Array(4-30).

However, this sheet does not always contain that many columns.  It does always begin at Array(4,  but ends anywhere from 6 to 32.

How can this be made dynamic to accommodate different numbers of columns ..??

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30), Replace _
:=True, PageBreaks:=False, SummaryBelowData:=True

Saturday, May 28, 2016 2:09 PM

• Try...

```    Dim aTotalList() As Integer
Dim iCol As Integer
With Selection.CurrentRegion
ReDim aTotalList(1 To .Rows(1).Cells.Count - 3)
For iCol = 4 To .Rows(1).Cells.Count
aTotalList(iCol - 3) = iCol
Next iCol
.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=aTotalList, _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With```

Hope this helps!

Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

• Marked as answer by Monday, May 30, 2016 3:54 PM
Saturday, May 28, 2016 11:24 PM

### All replies

• Try...

```    Dim aTotalList() As Integer
Dim iCol As Integer
With Selection.CurrentRegion
ReDim aTotalList(1 To .Rows(1).Cells.Count - 3)
For iCol = 4 To .Rows(1).Cells.Count
aTotalList(iCol - 3) = iCol
Next iCol
.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=aTotalList, _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With```

Hope this helps!

Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

• Marked as answer by Monday, May 30, 2016 3:54 PM
Saturday, May 28, 2016 11:24 PM
• Hi Mikf,

did you check the suggestion given by the Domenic Tamburino.

if you did not checked it then I would recommend you to check it first and let us know if it worked for you or not so that we can try to provide further suggestions.

Regards

Deepak

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.