This page is simply an extended example of descriptive information associated with the INSRTROW macro.

Example of an Excel Table ABCDE1DescriptionCountUnit PriceAmountRunning Total2Article-1 3 2.50 7.50 7.50 3Article-2 2 3.50 7.00 14.50 4Article-3 33 0.50 16.50 31.00 5Article-4 5 2.95 14.75 45.756Total45.757The above data view might appear as follows in the formula view. By coding in this fashion the use of the INSRTROW macro greatly simplifies the insertion of new rows.

ABCDE1DescriptionCountUnit PriceAmountRunning Total2Article-1 3 2.50 =B2*C2 =d2 3Article-2 2 3.50 =B3*C3 =offset(E3,-1,0)+D3 4Article-3 33 0.50 =B4*C4 =offset(E4,-1,0)+D4 5Article-4 5 2.95 =B5*C5 =offset(E5,-1,0)+D56Total=SUM(D2:offset(D7,-1,0)

Why must we use that funning looking OFFSET Worksheet FunctionOFFSET(reference, rows, cols, height, width)

In order to use the InsertRowsAndFillFormulas macro (INSRTROW macro), the formulas must be entered naming only the cellnames found on the current row. By referencing cells in the current row any insertion or deletion of lines will cause formulas to be updated. In order to refer to cells elsewhere but to name only cell addresses in the current row. See example above using OFFSET.

How to get into trouble inserting lines using simple formulasA simplified version of the above would appear as follows. I will be describing the problems with this simplified version below.

Simplified Example (avoid this form) ABCDE1DescriptionCountUnit PriceAmountRunning Total2Article-1 3 2.50 =B2*C2 =d2 3Article-2 2 3.50 =B3*C3 =E2+D3 4Article-3 33 0.50 =B4*C4 =E3+D4 5Article-4 5 2.95 =B5*C5 =E4+D56(leave blank) 7Total =SUM(D2:D6)Leaving a blank Row 6 allowed for easy insertion of a row without having to change the SUM formula.

In the next example the blank Row 6 will be removed and the SUM formula will use OFFSET to simply insertion of lines.

The above can be coded simply as follows:

ABCDE1DescriptionCountUnit PriceAmountRunning Total2Article-1 3 2.50 =B2*C2 =d2 3Article-2 2 3.50 =B3*C3 =E2+D3 4Article-3 33 0.50 =B4*C4 =E3+D4 5Article-4 5 2.95 =B5*C5 =E4+D56Total = SUM(D2:offset(D6,-1,0)7In the above example if a row were inserted the SUM formula would not need any attention, but you would have to fix up the row formulas. The result would be as follows::

ABCDE1Description Count Unit Price Amount Running Total 2Article-1 3 2.50 =B2*C2 =d2 3Article-2 2 3.50 =B3*C3 =E2+D3 45Article-3 33 0.50 =B5*C5 =E3+D5 6Article-4 5 2.95 =B6*C6 =E5+D67Total = SUM(D2:offset(D7,-1,0)Notice that E5 reads

=E3+D5instead of =E4+D5, and the formulas must be filled in manually for D4 and E4.Insert a Row using a Macro to maintain formulas

I wanted to be able to insert a row and maintain the formulas but not to copy data that would be unique for the line.

If we correct all formulas so that we can insert a row copying formulas and removing constants such as when using the INSRTROW macro described on another page, we would end up with formulas that are a lot easer to to work with -- no changes required. They would appear as follows in a formula view:

ABCDE1DescriptionCountUnit PriceAmountRunning Total2Article-1 3 2.50 =B2*C2 =d2 3Article-2 2 3.50 =B3*C3 =offset(E3,-1,0)+D3 4=B4*C4 =offset(E4,-1,0)+D4 5Article-3 33 0.50 =B5*C5 =offset(E5,-1,0)+D5 6Article-4 5 2.95 =B6*C6 =offset(E5,-1,0)+D67Total=SUM(D2:offset(D7,-1,0)The above formula view generated by the INSRTROW macro would appear as follows in the normal data view, simply fill in the Description, Count and Unit Price for the inserted Row.

ABCDE1DescriptionCountUnit PriceAmountRunning Total2Article-1 3 2.50 7.50 7.50 3Article-2 2 3.50 7.00 14.50 40.00 14.50 5Article-3 33 0.50 16.50 31.00 6Article-4 5 2.95 14.75 45.757Total45.758## DoubleClick Event Macro to insert a SUM total using OFFSET (#dclick)

The following is an Event macro and have used ActiveCell instead of target so you can copy code and paste into whatever macro you are using it in.Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target _ As Range, Cancel As Boolean) Cancel = True '-- Get out of Edit mode ActiveCell.Formula = "=SUM(" & Cells(5, _ ActiveCell.Column).Address(1, 0) _ & ":OFFSET(" & ActiveCell.Address(0, 0) & ",-1,0))" End Sub## Another way of specifying OFFSET without using OFFSET (#namedrange)

see this posting by Ian Brown using anamed rangefor PrevCell of !A1 where A2 is the activecell when defined. i.e. =SUM(H3:PrevCell)

INDEX(array, row_num, column_num)

=SUM(D2:D14) — use of a RANGE of course you would use OFFSET as shown above.a single area range is an array

INDEX(reference, row_num, column_num, area_num)reference is one or more cell ranges

=SUM("Motors") — specification of a range in a worksheet functionTo get to the third column in the first row of the range "Motors"

=INDEX("Motors", 1,3)The equivalent with OFFSET would be

=OFFSET("Motors",0,2)Use of

=INDEX((D1:F5,E3:G7),1,3,2) returns the value of cell G3

=INDEX((motors,E3:g7),1,3,1) if Motors is D1:F5, returns the value of cell F1Also see

- MATCH Worksheet Function
- VLOOKUP Worksheet Function page has some usage related to INDEX and MATCH as an alternative to VLOOKUP.

The use ofOFFSETallows you to refer to the previous row without actually using the previous row number in a cell address.

A300:`=SUM(A2:OFFSET(A300,-1,0)`

The use of

INDEXallows you to always add up to the row above without regard to typing in any specific cell address.

A300:`=SUM(A2:INDEX(A:A,ROW()-1))`

The use of

SUBTOTALallows you to ignore totals in other SUBTOTALS and, if filtered, it only includes filtered values (don't confuse filter with hidden)

A300:`=SUBTOTAL(9,A2:OFFSET(A300,-1,0)`

A300:`=SUBTOTAL(9,A2:INDEX(A:A,ROW()-1))`

The starting point A2 in column A, allows for headers on Row 1.

The use of INDEX in this manner was found in a posting by Gord Dibben (2005-05-03, worksheet.functions) appears to be orginally posted in English newsgroups by Hans Knudsen 1997-07-27

This page is an extension of Insert a Row using a Macro to maintain formulas which describes the INSRTROW macro code.

How to install/use a macro can be found on my formula

Creation of Shortcut keys spreadsheet and an Example from XL95. Same directions as in join.htm#shortcut.

You are one of many distinguished visitors who have visited my site here or in a previous location since March 24, 1999

Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]

Please send your comments concerning this web page to: David McRitchie
*
send email comments*

Copyright © 1997 - 2005, F. David McRitchie, All Rights Reserved