This page contains some VBA macros.  If you need assistance to install or to use a macro please refer refer to my  Formula  page.

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.  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.

Why must we use that funning looking OFFSET Worksheet Function

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

Code for the InsertRowsAndFillFormulas Macro

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

Background for INSRTROW macro (comment added 2000-02-01)

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

Worksheet_Change event

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

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

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

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

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

    =INDIRECT(C2)
    =INDIRECT("C"&1+1)

    =INDIRECT(address(row(C1)+1, column(C1))

Adjusting coding when named cells are used

    =OFFSET(pension,row(a2)-row($A$2),0)+OFFSET(taxable_income,row(A2)-row($A$2),0)

Bank Reconciliation by Check Numbers

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

	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).

Deleting Rows

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

    On Error Resume Next     ' In case there are no blanks
    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    ActiveSheet.UsedRange 'Resets UsedRange for Excel 97

  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

    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

Adding/Subtracting with sign on Right

=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 sign

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

Printing Multiple Labels based on Column A

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
  • Q140704 XL: How to Convert a Numeric Value into English Words can be pasted into Excel some versions had a problem with copying in that end of line indicators were missing (CRLF).
  • Q95640 ACC: How to Convert Currency or Numbers into English Words, MS KB article for Access works also for Excel.
  • Q231002 -- XL2000: How Auto Extend List Behavior Works
• Checkbook
  Q152407 XL: Using OFFSET, MATCH, and MAX to Return Last Value in Range see checkbook example
• 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 

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com