Insert a Row using a Macro to maintain formulasThe 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.
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.
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:
Normally the dates in Column B are entered using the shortcut key for the current date [Ctrl+semi-colon]. 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 FunctionIn 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:
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 MacroTo 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./dmcritchie/excel/insrtrow.htm
‘ Re: Insert Rows -- 1997/09/24 Mark Hill
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
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.
|
| 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.
|
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
![[Insert Row]](http://image.360doc.com/DownloadImg/18986/466137_2.gif){kind=small}
for InsertRowsAndFillFormulasSub 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.
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
=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.
=OFFSET(pension,row(a2)-row($A$2),0)+OFFSET(taxable_income,row(A2)-row($A$2),0)
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.
| 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. |
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
Rows("15:15").Insert Shift:=xlDown
‘insert cells based on content in A1‘
N = Range("A1").Value
ActiveCell.Resize(N).EntireRow.Insert
=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),"")
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)
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 SubSolution 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 IfXL2002 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 programmingFor 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
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
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
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.
|
|
