Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.
View products that this article applies to.
Sub TotalRowsAndColumns()
' This macro assumes that you have selected any cell or group of
' cells within a rectangular region of cells that you would like to
' have totaled. The totals will appear in the row below and the
' column to the right of the current region.
Dim r As Integer
Dim c As Integer
Dim i As Integer
Dim j As Integer
Dim myArray As Variant
' Declaring myArray as a Variant prepares it to receive a range of
' cells. At that point it is transformed automatically into an array
' with beginning subscript myArray(1,1).
'Refer to the region surrounding the current selection
With Selection.CurrentRegion
r = .Rows.Count
c = .Columns.Count
'resize for totals row and column and place into array
myArray = .Resize(r + 1, c + 1)
' The following 10 lines of macro code (including comments)
' are what is commonly referred to
' as a Nested Loop. The variable i keeps track of the row number,
' while j keeps track of the column number. Every time j cycles
' through the available columns, i gets incremented by one and j
' starts the cycle from one to c all over again.
For i = 1 To r
For j = 1 To c
'total for row i
myArray(i, c + 1) = myArray(i, c + 1) + myArray(i, j)
'total for column j
myArray(r + 1, j) = myArray(r + 1, j) + myArray(i, j)
'grand total
myArray(r + 1, c + 1) = myArray(r + 1, c + 1) + myArray(i, j)
Next j
Next i
' Return the array, which now contains an extra row and column
' for the totals, to the worksheet.
.Resize(r + 1, c + 1) = myArray
End With
End Sub
Keywords: KB213472, kbprogramming, kbinfo, kbhowto