An Excel Subtotal Question 1

[Kurjo]

Hi,

I am a beginner in excel programming. Now I have to do some thing for my current project.

I am creating an excel sheet dynamically using the Database application. Now I have to subtotal this.

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
9, 10, 11, 12, 13, 14), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Here the GroupBy:= will change sheet to sheet, but that I can pass as an argument.

TotalList:=Array(5,6...) will varies and it is not possible to pass an argument since it may come upto 60 columns, until the rightmost column in the sheet.

How can I programatically add these array list in the macro.

Any sugestions or lead to another faq is highly appreciated.

 

[SkipVought]

Hi,

Well, SOMEWHERE you have to specify which columns get TOTALED! It could be in a list based on a sheet name criteria. So then based on the sheet you are processing, you get the corresponding column values.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Kurjo]

Hi Skip,

Thanks for the reply.
If I find the columns say D:Q that are need to be totaled, how do I apply in this subtotal code?

 

[SkipVought]

Here's how...

Option Base 1
Sub SubtotalsOnSheets()
    Dim arry()
    For Each ws In Worksheets
        With ws
            Select Case .Name
                Case "Sheet10", "Sheet11"
                i = 0
                For Each c In [Sheet]
                    If c.Value = .Name Then
                        i = i + 1
                        If i = 1 Then
                            ReDim arry(i)
                        Else
                            ReDim Preserve arry(i)
                        End If
                        arry(i) = c.Offset(0, 1).Value
                    End If
                Next
                ws.Cells(1, 1).Subtotal _
                    GroupBy:=1, _
                    Function:=xlSum, _
                    TotalList:=arry, _
                    Replace:=True, _
                    PageBreaks:=False, _
                    SummarryBelowData:=True
            End Select
        End With
    Next
End Sub

where named range Sheet has your sheet names and the adjacent column has column number



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

D:Q is not a good way to state this, since not all totaled column would ALWAYS be a contiguous column range.

My example has column number.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Kurjo]

Tnaks again Skip. It is a good code I will use it.