Insert a Row using a Macro to maintain formulas
The macro InsertRowsAndFillFormulas() has been updated 2000-09-02
INSRTROW is a macro to copy the selected line making the requested number of copies of it downward, but to include only the formulas. Leaving the constants to be manually filled in. (
button )
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. The propagation of data would be misleading so I want it to be blank so I have to enter it myself.
See also use of OFFSET in formulas which simplifies the insertion, and deletion of rows with the InsertRowsAndFillFormulas macro and allows you to sort the rows; whereas, sorting rows containing simple addition and subtraction of relative addresses would make a mess.
| | A | B | C | D | E | F | G | H | I |
| 1 | | Date | Check# | Descriptions | DR | CR | Balance | Reconciled | stmt |
| 2 | 3 | 01/20/1998 | DEP | Initial Balance | 2000.00 | | 2000.00 | 02/27/98 | 2000.00 |
| 3 | 6 | 02/06/1998 | Debit | Phone Co. | | 18.22 | 1981.78 | 02/27/98 | 1981.78 |
| 4 | 2 | 02/23/1998 | 2619 | Gas/Electricity | | 117.97 | 1863.81 | | 1981.78 |
Want to insert a line so select row 2 or any cell on row 2 such as D2, then hit the macro button to invoke InsertRowsAndFillFormulas.
| | A | B | C | D | E | F | G | H | I |
| 1 | | Date | Check# | Descriptions | DR | CR | Balance | Reconciled | stmt |
| 2 | 3 | 01/20/1998 | DEP | Initial Balance | 2000.00 | | 2000.00 | 02/27/98 | 2000.00 |
| 3 | 7 | | | | | | 2000.00 | | 2000.00 |
| 4 | 6 | 02/06/1998 | Debit | Phone Co. | | 18.22 | 1981.78 | 02/27/98 | 1981.78 |
| 5 | 2 | 02/23/1998 | 2619 | Gas/Electricity | | 117.97 | 1863.81 | | 1981.78 |
The formulas have been copied but cells that do not contain formulas are empty, and the data can now be entered with the results as follows:
| | A | B | C | D | E | F | G | H | I |
| 1 | | Date | Check# | Descriptions | DR | CR | Balance | Reconciled | stmt |
| 2 | 3 | 01/20/1998 | DEP | Initial Balance | 2000.00 | | 2000.00 | 02/27/98 | 2000.00 |
| 3 | 5 | 02/05/1998 | 2618 | Subscription | | 26.95 | 1973.05 | | 1981.78 |
| 4 | 6 | 02/06/1998 | Debit | Phone Co. | | 18.22 | 1954.83 | 02/27/98 | 1981.78 |
| 5 | 2 | 02/23/1998 | 2619 | Gas/Electricity | | 117.97 | 1836.86 | | 1981.78 |
Normally the dates in Column B are entered using the shortcut key for the current date [Ctrl+semi-colon].
When using a Debit card enter a letter instead of the date into the reconciled column. Change the letter to an actual date when you have banks entry.
My DR/CR are the opposite of what most checkbooks show, since I prefer to keep records for myself and not for my bank or their point of view.
¤ If you maintain a checkbook in Excel you can add up deposits for a deposit slip by selecting the range and looking at the SUM= on the status bar.
OFFSET(reference, rows, cols, height, width)In order to use the InsertRowsAndFillFormulas 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, OFFSET will be used as follows:
| A3 | =IF(B3="",">",WEEKDAY(B3,1)) |
| | |
| G3 | =OFFSET(G3,-1,0)+E3-F3 |
| | instead of |
| | =G2+E3-F3 |
| | |
| | Optionally assume 5 days to clear |
| H3 | =IF(NOW()>B3+5,"*","") |
| | Fill in a real date when you have it |
| | |
| I3 | =IF(H3<>"",OFFSET(I3,-1,0)+E3-F3,OFFSET(I3,-1,0)) |
| | instead of |
| | =IF(H3<>"",I2+E3-F3,I2) |
| | |
| | Optionally include a low balance warning in RED |
| J3 | =IF(OR(G3<250,I3<300),"XX",""),"") |
Replication is made easier by grabbing and dragging the fill handles and with the use of shortcuts Ctrl+D (Fill Down), and Ctrl+R (Fill Right). OFFSET makes Replication/Deletion of Rows tie in better with the other rows, and of course, makes possible the InsertRowsAndFillFormulas macro described in this page.
A more explicit example of OFFSET and using the macro described below can be found in Using OFFSET to maintain formulas
To obtain a macro that would do what I wanted I posted to the newsgroup what I wanted and failed at that. I then found and included a macro close to what I wanted and described again exactly what I really needed. This worked very well and the solution involved a one line addition that removed non formulas from the inserted line providing exactly what I wanted.
The macro is installed on a module sheet in my personal.xls The code below has extra blank lines to help identify long lines it they get wrapped when you copy them. For more information on why INTERSECT and how SpecialCells are used in the following macro see comments with Proper_Case macro.
Sub InsertRowsAndFillFormulas(Optional vRows As Long) ‘ Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ‘ Re: Insert Rows -- 1997/09/24 Mark Hill ‘Dim vRows As Integer ‘ row selection based on active cell -- rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select ‘So you do not have to preselect entire row If vRows <> 1 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", Title:="Add Rows", _ Default:=1, Type:=1) ‘type 1 is number If vRows = False Then Exit Sub End If ‘if you just want to add cells and not entire rows ‘then delete ".EntireRow" in the following line ‘rev. 20001-01-17 Gary L. Brown, programming, Grouped sheets Dim sht As Worksheet, shts() As String, i As Integer ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 For Each sht In _ Application.ActiveWorkbook.Windows(1).SelectedSheets Sheets(sht.Name).Select i = i + 1 shts(i) = sht.Name Selection.Resize(rowsize:=2).Rows(2).EntireRow. _ Resize(rowsize:=vRows).Insert Shift:=xlDown Selection.AutoFill Selection.Resize( _ rowsize:=vRows + 1), xlFillDefault On Error Resume Next ‘to handle no constants in range -- John McKee 2000/02/01 ‘ to remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht Worksheets(shts).Select End Sub
Addition insertion of rows, Examples:
Sub InsertBeforeTotalinColumnA() Columns("A:A").Find(What:="total", After:=Range("A2"), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Offset(-1, 0).Activate Call InsertRowsAndFillFormulas(1) ‘see my insrtrow.htm page End Sub Sub Macro18() Dim C As Long C = 3 ‘Number of rows to insert Range("b15").Select ‘below this cell ActiveCell.Offset(1, 0).EntireRow. _ Resize(rowsize:=C).Insert Shift:=xlDown End Sub Install a button to invoke macro for InsertRowsAndFillFormulas
I created a macro button that has colors and resembles a inserted line Refer to
toolbars page for some help in setting up such a button.
Background for INSRTROW macro (comment added 2000-02-01)
Creating such a macro proved difficult and I could not get the answers I wanted for a generic solution from the newsgroup.
http://groups.google.com/groups?oi=djq&ic=1&selm=an_327694444 So I searched what is now Google archives for the closest solution (1997/09/24 Mark Hill) to my problem and then posted that code with a description of what I really wanted and was surprised that a one line addition (1998/03/11 Bill Manville) would remove the constants. Aren‘t newsgroups great!
Also from Bill‘s reply learned to use OFFSET in formulas to remove the need to modify formulas after running the macro. A small price to pay for the benefit. http://groups.google.com/groups?oi=djq&ic=1&selm=an_333104660
When you think everything is done, find out that the macro will not work for grouped (multiple selected) sheets. Gary L. Brown, 2001-01-16, added looping through the selected sheets; and I sloppily corrected that to reselect the sheets in the group before macro was run.
Some customized changes
‘---- additionally clear out columns B and D in the inserted rows ‘---- Columns B & D were entered as formulas, but are to be cleared anyway Dim multi_range As Range Dim new_area As Range Set multi_range = Application.Union(Range("b:b"), Range("d:d")) Set new_area = Selection.Offset(1).Resize(vRows).EntireRow Intersect(new_area, multi_range).ClearContents To retain constants, code as formulas: i.e. ="abc"
Worksheet_Change event
The following Worksheet_Change event could be used to create the formulas on the line when the date is typed into the B column. The InsertRow macro is still preferable because the copy will copy the formatting for cells (number & colors). More on Change Events on my
event.htm page.
A change event will not recognize entry of a date with a short-cut (Ctrl+;), nor will it recognize a change by pasting a value.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) ‘to install -- rightclick on the sheettab of the corresponding ‘ sheet and choose ‘view code‘. Paste the following procedure ‘ in the module. If Target.Column <> 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Dim R As Long R = Target.Row Target.Offset(0, -1).Formula = _ "=IF(B" & R & "="""","">"",WEEKDAY(B:b,1))" Target.Offset(0, 5).Formula = _ "=OFFSET(G" & R & ",-1,0)-F" & R & "+E" & R Target.Offset(0, 6).Formula = _ "=IF(NOW()>B" & R & "+5,""*"","""")" Target.Offset(0, 10).Formula = _ "=IF(OR(G" & R & "<250,M" & R & " <300),""XX"","""")" Target.Offset(0, 11).Formula = "" then _ Target.Offset(0, 11).Formula = _ "=IF(H" & R & "<>"""",OFFSET(M" & R & ",-1,0)+E" _ & R & "-F" & R & ",OFFSET(M" & R & ",-1,0))" ‘=IF(H604<>"",OFFSET(M604,-1,0)+E604-F604,OFFSET(M604,-1,0)) Target.Offset(0, 12) = R End Sub
Extended Formats and Formulas
Related to automatically entering formulas, this may be useful for some applications. I found the option very annoying.
It would not be practical for examples on this page as when I find out what the actual posting date is, I substitute that into the cell in place of formula generated space or asterisk. Also a column I add so that I could sort on if I mixed things up gets automatically extended.
| Turn on or off extended formats and formulas When you turn on extended formats and formulas, Microsoft Excel automatically formats new data that you type at the end of a list to match the preceding rows and automatically copies formulas that repeat in every row. To be extended, formats and formulas must appear in at least three of the five list rows preceding the new row. - On the Tools menu, click Options, and then click the Edit tab.
- Do one of the following: To automatically format new items that you add to the end of a list to match the format of the rest of the list, select the Extend list formats and formulas check box.
To prevent automatic formatting, clear the check box. |
Actually I now have it turned on, it probably is just a matter of getting used to it. You might take a look at Q231002 -- XL2000: How Auto Extend List Behavior Works
Some Macros for inserting blank rows on alternate lines
The following macro will insert a row BEFORE any row in which the cell in Column A is not blank, with the exception of before the first row. It inserts lines from the bottom of the Used range upward so inserted lines do not interfere with counting. Blank here is anything that looks like a blank. If you want to insert regardless of content in Column A, then remove the condition.
Sub InsertALTrows() ‘David McRitchie, misc 2000-01-27 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ‘pre XL97 xlManual Dim i as long For i = Cells.SpecialCells(xlLastCell).Row To 2 Step -1 If Len(Trim(Cells(i, 1))) <> 0 Then Rows(i).Insert ‘ 1 is Column A Next i Application.Calculation = xlCalculationAutomatic ‘pre XL97 xlAutomatic Application.ScreenUpdating = True End Sub
Insert1or2RowsBeforeNonBlankA() use the following code to insert 1 or 2 rows before the any value in Column A. If there is a value in A immediately above only
one row; otherwise,
2 rows.
For i = Cells.SpecialCells(xlLastCell).Row To 2 Step -1 If Len(Trim(Cells(i, 1))) <> 0 Then If Len(Trim(Cells(i - 1, 1))) <> 0 Then Rows(i).Insert Shift:=xlDown Else Rows(i + 1).Resize(2).Insert Shift:=xlDown End If End If
If there is a value in A immediately above; oth othnav The following macro will insert a row between existing rows in a selection any row
Sub InsertALTrowsInSelection() ‘David McRitchie, misc 2001-06-30 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ‘pre XL97 xlManual Dim i As Integer For i = Selection(Selection.Count).Row To Selection(1).Row + 1 Step -1 Rows(i).Insert Next i Application.Calculation = xlCalculationAutomatic ‘pre XL97 xlAutomatic Application.ScreenUpdating = True End Sub
An alternative to this may simply be to increase the height of all rows to provide some spacing that would not be lost due to sorting.
Another alternative might be the use of Conditional formatting to color alternate rows:
=INT(ROW($A1)/2)*2=ROW($A1)
also see Coloring within Ranges.
Insert Blank Rows on Column A change of value
Sub InsertRow_A_Chg() Dim irow As Long, vcurrent As String, i As Long ‘// find last used cell in Column A irow = Cells(Rows.Count, "A").End(xlUp).Row ‘// get value of that cell in Column A (column 1) vcurrent = Cells(irow, 1).Value ‘// rows are inserted by looping from bottom For i = irow To 2 Step -1 If Cells(i, 1).Value <> vcurrent Then vcurrent = Cells(i, 1).Value Rows(i + 1).Insert End If Next i End Sub
Related coding
Is there a way to calculate a cell reference? i.e. +C2 calculated as +C(1+1)
=INDIRECT(C2) =INDIRECT("C"&1+1) =INDIRECT(address(row(C1)+1, column(C1)) The inclusion of address helps correct formulas if you insert or delete lines.
Adjusting coding when named cells are used
If the formula to be replicated starts in A2, try the following:
=OFFSET(pension,row(a2)-row($A$2),0)+OFFSET(taxable_income,row(A2)-row($A$2),0)
Bank Reconciliation by Check Numbers
Thought you might find this one interesting. VLOOKUP was suggested by Myrna Larson in the MISC newsgroup.
Problem: Reconcile bank statement. Two files received electronically. One includes checks issued with check number in Column A, and the other file has checks paid and also includes the check #. How can the two columns of check numbers be compared to show outstanding checks.
Solution: Try a VLOOKUP formula with the last argument set to 0. If a check isn‘t found in the bank‘s list, the formula will show #NA.
I like my checkbook worksheet, but if you don‘t here is a program that keeps track of type transaction as well Easy Checkbook for Excel PC World.
Inserting Lines -- Manual Alternative
For more on shortcuts see my shortcut pages
shortx2k, and
shortx95 for additional information.
| | XL2000 Keyboard Shortcuts | Keystrokes to invoke | Menu |
| | Insert blank cells | CTRL+SHIFT+PLUS SIGN | Insert --> Rows |
| | Fill down | CTRL+D | Edit--> Fill--> Down |
| The advantage with the macro includes, being able to insert lines below rather than above, being able to specify number of rows to insert rather than selecting the exact number of rows to insert, and to remove the constant information while retaining the formulas which reduces problems with inadvertant duplicate entries. |
Manually removing constants
- Select the range (or the entire sheet) where constants are to be removed.
- Edit => Goto (F5) => Special and select constants. (Ctrl+G can also be used for Goto)
- Edit => Clear => Contents (or just hit the delete key).
To preserve some constants and information from the above removal, you can code:
Provide numeric constant in a formula i.e. =3.14
Provide text constant in a formula i.e. =text("abc def","@")
Use some text boxes to provide information.
Deleting Rows
The antithesis of InsertRowsAndFillFormulas described here is
DeleteBlankRows in
Chip Pearson‘s Excel pages. Caution: I had to comment out two lines beginning with Application.Calculation to avoid a runtime error 1005 on my system (XL95 under WinNT). Also check out an article by John Walkenbach
Delete All Empty Rows in a Flash (Oct 1997 PC World). If you delete rows at end of spreadsheet you should also review my article
Reset Last Cell Used.
If you are going to delete rows you will want to work your way up from the bottom, so you don‘t trip yourself up by skipping rows you haven‘t examined or by deleting the wrong row.
dim rw as long For rw = ActiveSheet.UsedRange.Rows.Count to 1 step -1 if cells(rw,"A")="" then rows(rw).delete next
The above loop is really not needed, read more about this in
delempty.htm (Dana DeLouis)
On Error Resume Next ‘ In case there are no blanks Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete ActiveSheet.UsedRange ‘Resets UsedRange for Excel 97 Delete All Rows that are completely empty
Sub RemoveEmptyRows() Application.ScreenUpdating = False ‘xlManual below in Xl95 Application.Calculation = xlCalculationManual Dim rw As Long, iCol As Long For rw = ActiveSheet.UsedRange.Rows.Count To 1 Step -1 If Application.CountA(Rows(rw).EntireRow) = 0 Then _ Rows(rw).Delete Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True ‘xlAutomatic above in xl95 End Sub
Within your own existing code possibilities include:
Selection.Rows.EntireRow.Delete ActiveCell.Rows.EntireRow.Delete Cells(16, 2).Rows.EntireRow.Delete Selection.EntireRow.Delete
More on Inserting Rows
Rows("15:15").Insert Shift:=xlDown ‘insert cells based on content in A1‘ N = Range("A1").Value ActiveCell.Resize(N).EntireRow.Insert Transactions came in from my new bank with a single entry column (col C), and a Balance column (Col D). The following is placed in Column E to verify the figures supplied in column D to make sure all transactions are present. Actually I used Replace ALL to remove + from Balance column and $ from Transaction and Balance Column.
=IF(RIGHT(C5,1)="-",E4-LEFT(C5,LEN(C5)-1),
IF(RIGHT(C5,1)="+",E4+LEFT(C5,LEN(C5)-1),E4+C5))
| | A | B | C | D | E |
| 1 | Date | Description | Amount | Balance | Verification |
| 2 | 08/19/1999 | BEGINNING BALANCE | | 600.00 | 600.00 |
| 3 | 08/19/1999 | PURCHASE GROCER #1111 | 33.42- | 566.58 | 566.58 |
| 4 | 08/30/1999 | CHECK NUMBER #101 | 190.96- | 375.62 | 375.62 |
| 5 | 09/05/1999 | PURCHASE GROCER #1111 | 67.05- | 308.57 | 308.57 |
Extending the example a little, the point is that a macro is not used, the purpose is to leave the original transaction data from the bank untouched, but the verification will make sure that everything is included as long as it matches the banks figure.
Date------ Description------ Amount* Balance Verification Checks** Loc. 10002 xx/xx/1999 CHECK NUMBER nnnn $46.00- $269.66+ 269.66 CHK nnnn 10020 xx/xx/1999 PURC. store #xxxx $53.56- $216.06+ 216.06 #xxxx 10021 =IF(RIGHT(C8,1)="-",E7-LEFT(C8,LEN(C8)-1),IF(RIGHT(C8,1)="+",E7+LEFT(C8,LEN(C8)-1),E7+C8)) =IF(LEFT(B8,13 )="CHECK NUMBER ","CHK " & MID(B8,14,99),"") =IF(LEFT(RIGHT(B8,5),1)="#",RIGHT(B8,5),"")
Fix Right Minus -- A Worksheet solution
This is a continuation of the previous topic and example. Following is a Worksheet solution to display a single value from another cell. =IF(RIGHT(D12,1)="-",-1*LEFT(D12,LEN(D12)-1)+0,D12)
FixRightMinus -- A Macro solution
Change right minus text constant cells in selection to negative numbers. You can change formatting before or after running the macro. Sub FixRightMinus() ‘David McRitchie 2000-02-04 rev 2000-05-01 ‘ rev. based on Dana DeLouis and Peter Surcouf ‘ prior to XL97 use xlManual and xlAutomatic Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range On Error Resume Next For Each cell In Selection.Cells.SpecialCells(xlConstants, xlTextValues) cell.Value = CDbl(cell.Value) Next cell Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
Solution above was revised based on solution which uses CDbl and On Error Resume from (Peter Surcouf and Dana DeLouis), which converts entire sheet rather than selected range. In any case the following is redundant and is replaced by one line. If Right(cell.Value, 1) = "-" Then cell.Value = -1 * Left(cell, Len(cell.Value) - 1) End If
XL2002 has a button [Advanced], on wizard panel to indicate trailing minus (when you tell it what format General|Date|skip|etc). Dave Peterson 2002-01-02 programming For those who want to do the entire workbook.
Sub CycleSheetsForRightMinus() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim ws As Worksheet Dim cell As Range For Each ws In ActiveWorkbook.Worksheets Worksheets(ws.Name).Activate On Error Resume Next ‘have changed selection.cells to cells For Each cell In Cells.SpecialCells(xlConstants, xlTextValues) cell.Value = CDbl(cell.Value) Next cell Next ws Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
Clear certain columns on active row
Since INSRTROW the focus of this page clears out constants, I thought this macro might be of interest. It will indiscriminently delete particular columns on the row of the active cell and will not change the active cell. Sub ClearCells() ‘leo.heuser@get2net.dk, misc, 2000-11-22 ‘ http://groups.google.com/groups?oi=djq&ic=1&selm=an_696636903 Dim ClearRange As Range Dim Area As Range Dim RowNumber As Variant Set ClearRange = Range("B:I,N:O") RowNumber = ActiveCell.Row If RowNumber > 1 Then For Each Area In ClearRange.Areas Area.Rows(RowNumber).ClearContents Next Area End If ActiveCell.Activate Set ClearRange = Nothing Set Area = Nothing End Sub
Maintaining a Total at two Rows below last input data in Column A
Use this formula in your last row i.e. last row is 20. (posted 2001-05-01 D.McRitchie)
A20: ‘Total
C20: =SUM(C2:OFFSET(C20,-1,0)) Install the following event macro on the sheet, so that when the cell above the last cell in Column A is filled in a row will be inserted before the last last cell in Column A. This should work better than actually using the last cell row, since last cell problems happen. This example will not replicate formulas.
Private Sub Worksheet_Change(ByVal Target As Range) ‘to install -- rightclick on the sheettab of the sheet to ‘ be used in and choose ‘view code‘. Paste this Worksheet ‘ event macro into the module. If Target.Column <> 1 Then Exit Sub ‘find last cell in column -- not going to use the last cell row If Cells(Cells(Rows.Count, 1).End(xlUp).Row - 1, 1).Value <> "" Then Rows(Cells(Rows.Count, 1).End(xlUp).Row).Insert End If End Sub
Inserting Two Blank Rows
The following is from Dana DeLouis, 2001-05-13, programming, where he points out that Resize is faster. Dana frequently comes up with time saving tips. If you look at the posting he also includes a programmed approach that does not involve a loop. Sub InsertTwoRows_v2() ‘Dana De Louis, programming, 2001-05-13 Dim r As Long For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1 Rows(r).Resize(2).Insert Next End Sub
See macro RepeatRowsOnColumnA in Mail Merge for a macro to repeat rows based on number in Column A, which would be needed by Mail Merge to print multiple labels with same content.
Related
- Excel 2000 provides an option to extend formulas, which I tried and did not like it one bit, but it is available: tools / options / Edit / extend list and formulas
- Check writing protection
Checkbook Delete Cells/Rows in Range, based on empty cells, also massively inserting rows, deleting rows based on multiple selected areas.
Fill-Handle, and techniques for moving rows or columns; and inserting blank rows or columns all using the cursor in combination with the ctrl, shift, or alt keys
