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 Let r = .Rows.Count Let c = .Columns.Count'Resize for totals row and column and place into array. Let myArray = .Resize(r + 1, c + 1)' In the following 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 Let myArray(i, c + 1) = myArray(i, c + 1) + myArray(i, j)'total for column j Let myArray(r + 1, j) = myArray(r + 1, j) + myArray(i, j)'grand total Let 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. Let .Resize(r + 1, c + 1) = myArrayEnd With End Sub
Quaisquer soluções e/ou desenvolvimento de aplicações pessoais, ou da empresa, que não constem neste Blog podem ser tratados como consultoria freelance.
VBA Excel - Armazenando os valores de um Range numa Matriz - Total Rows and Columns in an Array
Tags: VBA, Excel, cell, array, Total,