Add Worksheet Column Grand Totals Using Range Variables
If you are serious about learning range programming, this book is a must!!
Most Excel accounting applications require "footers" (grand totals for columns of numbers). These footers normally have underlines in standard accounting format. When you use the macro recorder to show how to sum one or more columns of numbers, Excel generates the same code for each column and uses Select methods and selection properties. By using range variables, you can simplify the process and condense the code into fewer statements.
In the example below, we are summing and creating grand totals for 8 columns of numbers. After the totals are created, we add the standard accounting borders (a line under the last number to be summed, with two lines under the actual total for the column).
Program Code
' *************************************************
' Determine The Number Of Rows In The Worksheet
' *************************************************
lngNumberOfRowsInTemplate = wksMainSheet.UsedRange.Rows.Count
lngNumberOfRowsInTemplate = lngNumberOfRowsInTemplate + wksMainSheet.UsedRange.Row - 1
' ************************************************************************
' Set the Range Variable To The Location of Grand Totals for 8 Columns
' This range is one row below the last number in the column(s)
' In the example below, we are summing columns 5 through 12 (E through L)
' ************************************************************************
Set rngSubtotals = Range(wksMainSheet.Cells(lngNumberOfRowsInTemplate + 1, 5), wksMainSheet.Cells(lngNumberOfRowsInTemplate + 1, 12))
' **************************************************************
' Add the formula to compute the totals of all 8 columns
' This worksheet has 4 header lines that are not summed
' **************************************************************
rngSubtotals.FormulaR1C1 = "=SUM(R[-" & lngNumberOfRowsInTemplate - 4 & _
"]C:R[-1]C)"
' **************************************************************
' Add Underlines to the Grand Totals Just Created
' **************************************************************
Set rngBorders = Range(wksMainSheet.Cells(lngNumberOfRowsInTemplate + 1, 5), wksMainSheet.Cells(lngNumberOfRowsInTemplate + 1, 12))
With rngBorders.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rngBorders.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With