Microsoft KB Archive/213472

= XL2000: How to Use Visual Basic Code to Total Rows and Columns in an Array =

Article ID: 213472

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213472





SUMMARY
In Microsoft Excel, you can use arrays to calculate and manipulate data in a worksheet. You can also use Microsoft Visual Basic for Applications to store the values from a range of cells in a Visual Basic array.

The sample macro code in this article adds an additional column and row to a rectangular region of cells that will contain totals for each row and column in that region.



MORE INFORMATION
The following Visual Basic code reads data from the current region of cells surrounding the active cell on the active worksheet. The macro stores the data in an array, and then sums each row and column, placing the output on the worksheet. The size of the array is determined by the number of cells in the current region.

To run the macro, follow these steps:
 * 1) Highlight a cell or range of cells within the region that you want to sum, point to Macro on the Tools menu, and then click Macros.
 * 2) Click to select the TotalRowsAndColumns macro, and then click Run.

NOTE: This macro does not enter any formulas into your worksheet, so if the numbers in the range being totaled change, you must run the macro again.

Macro Example
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

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 To perform an operation similar to the one performed in this example, modify the macro code. For example, to subtract, multiply, or divide the values contained in the selected range of cells, you need to change the mathematical operator.

Additional query words: XL2000

Keywords: kbhowto kbinfo kbprogramming KB213472

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.