CODEWITHSUNDEEP
excelvba
pattalol
pattalol

Reputation: 13

Summarizing column values in a table

I have buttons that can add rows, undo last added row and delete all rows from a table. Lastly, I have a button that should add a row at the end of the table which sums the contents of the columns above. 

Private Sub cmbSummarizeColumns_Click()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow

Set the_sheet = Sheets("Ark1")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add

table_object_row.Range(1, 1).Value = sumcolumn_1
table_object_row.Range(1, 2).Value = sumcolumn_2
table_object_row.Range(1, 3).Value = sumcolumn_3
End Sub

What I dont know is how I can find this sum of column 1,2 and 3, as I'm quite new to VBA. And yes, I have headers in this table. Any help would be appreciated.

Upvotes: 0

Views: 3070

Answers (1)

user4039065
user4039065

Reputation:

If you are going to add a row for the totals, you may as well add a structured ListObject TotalsRowRange property.

Private Sub cmbSummarizeColumns_Click()
    Dim the_sheet As Worksheet
    Dim table_list_object As ListObject
    Dim tblCOL As Long

    Set the_sheet = Sheets("Sheet6")
    Set table_list_object = the_sheet.ListObjects(1)

    With table_list_object
        If Not .ShowTotals Then .ShowTotals = True
        For tblCOL = 1 To 3   'might use To .DataBodyRange.Columns.Count
            .TotalsRowRange.Cells(1, tblCOL) = _
              Application.Sum(.DataBodyRange.Columns(tblCOL))
        Next tblCOL
    End With
End Sub

The sample you provided seemed to indicate that you wished to hard code the sum values. If you want a SUM function or SUBTOTAL function instead, it should be an easy matter of substituting a formula for the values returned by the VBA evaluated formula.

Upvotes: 1

Related Questions