Location: http://www.mvps.org/dmcritchie/excel/insrtrow.htm Code: http://www.mvps.org/dmcritchie/excel/code/insrtrow.txt Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]
[offset], [insrtrow] [dclick], [copyformulas], [InsertBlankRows], [moreresize], [ChangeEvent], [extended], [AltBlanks], [ColAchg], [before_a], [reconcile], [ManualInsert], [DeleteK], [DeleteR], [addendum], [sign_onright], [fixrightminus], [clearCols], [extraTotals], [blankrows], [multilabel], [#VALUE!], [carrybalance], [related],
This page contains some VBA macros. If you need assistance to install or to use a macro please refer refer to my Getting Started with Macros page. Standard macro can be invoked from any worksheet.
This page also contains Worksheet EVENT macros, which are installed differently. Worksheet event macros are designed for a specific worksheet and only apply to the worksheet they are installed into. Examples change event, double-click, cell change, right-click.
The macro InsertRowsAndFillFormulas() had a major update on 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 US checkbooks show, since I prefer to keep records for myself and not for my bank or their point of view. At one time you could ask the bank to provide with with a properly labelled checkbook, but now you would probably have toe chenage the titles yourself. You can see a representation of what your checkbook and a bank statement might look like -- Pain-free Bank Reconciliations - MyWebCA, by David Trahair, C.A., it is a Canadian site, but that was the easiest for me to find).
¤ 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.
The lazy way of keeping your bank balances is to use Quicken (or MS Money) and download records from you financial institutions. Unfortunately you would not see both dates -- the date on your check and the date it cleared.
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 the next topic.
A more explicit example of OFFSET and using the macro described below can be found in Using OFFSET to maintain formulas also includes use of INDEX in totals and subtotals to add up to the row above without regard to typing in any specific cell address as in
=SUM(A2:OFFSET(A300,-1,0)in cell A300.
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 primary macro InsertRowsAndFillFormulas is installed on a toolbar button.
Both macros are 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 macros see comments with Proper_Case macro. The following macro pair is the main focus of this web page; however, there is also a Change event macro that may be of interest (further down).Sub InsertRowsAndFillFormulas_caller() '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog Call InsertRowsAndFillFormulas End Sub Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ' Re: Insert Rows -- 1997/09/24 Mark Hill <firstname.lastname@example.orgSpam> ' row selection based on active cell -- rev. 2000-09-02 David McRitchie Dim x as long ActiveCell.EntireRow.Select 'So you do not have to preselect entire row If vRows = 0 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", Title:="Add Rows", _ Default:=1, Type:=1) 'Default for 1 row, 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. 2001-01-17 Gary L. Brown, programming, Grouped sheets Dim sht As Worksheet, shts() As String, i As Long 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 x = Sheets(sht.name).UsedRange.Rows.Count 'lastcell fixup 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 SubNote that there is an optional parameter that can be used from another macro. It would be best to keep the macro as is for general use, but when calling from a macro to have the calling macro include the number of rows to be inserted and no MsgBox.
Invoking the macroSelect the row above where you want to insert the rows, this is also the row whose formulas will be copied down.
The first time you test you would probably invoke the macro ... from Tools, Macros, macros (Alt+F8) choosing ...
For easier use in invoking the InsertRowsAndFillFormulas macro, I use a toolbar button — A display of my custom buttons can be seen on that page.
The following is an Event Macro, and will insert 3 rows without prompting, this one is triggered by a Double Click. (see next two topics about installaton of an Event macro)Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True 'Get out of edit mode Application.Run "personal.xls!InsertRowsAndFillFormulas", 3 End Sub
The following macro will clear the constants from selection area(s). You can install it in a toolbar menu, a toolbar button, or a right-click menu. The code for this macro will be with the code associated with rightclick page, but the coding below is relevant to material on the page you are currently viewing, as it is the same as the primary feature in insert rows and retain formulas.Sub Clear_Constants() 'D.McRitchie 2005-11-19 rightclick.htm - insrtrow.htm '-- provide for in rightclick cell, row, and column commandbars Dim rng As Range 'prevent expansion of a single cell selection Set rng = Intersect(Selection, Selection.SpecialCells(xlConstants), ActiveSheet.UsedRange) If rng Is Nothing Then MsgBox "No constants in selection area(s) -- no removal" Else rng.ClearContents End If End SubTo manually remove constants from a selection, see later topic -- Manually removing constnts from a selection.
Event macros are installed differently that standard macros. Each of the Event macros below are Worksheet Event Macros and can be installed by right clicking on the worksheet tab then View Code, then insert the event macro. More information on Event macros.
Example of an Event Macro that simply inserts a row below the current row, and populates it with the formulas, by copying and wiping out the constants. (#dclick)Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True 'Eliminate Edit status due to doubleclick Target.Offset(1).EntireRow.Insert Target.EntireRow.Copy target.Offset(1).EntireRow on error resume next Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents on error goto 0 End SubSame example but insert 3 rows copying formulasPrivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True 'Eliminate Edit status due to doubleclick target.Offset(1).EntireRow.Resize(3).Insert target.EntireRow.Copy target.EntireRow.Offset(1).Resize(3).EntireRow on error resume next target.Offset(1).EntireRow.Resize(3).SpecialCells(xlConstants).ClearContents on error goto 0 End SubThe following Worksheet DoubleClick Event Macro will copy formulas down from the last row, and increment the value in Column A of the last row by 7 days for the new row then place the cursor in the first empty cell of the new row. The last row is determined by having content in Column A.Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim lastrow As Long 'D.McRitchie, 2004-06-10, newusers lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault on error resume next Rows(lastrow + 1).SpecialCells(xlConstants).ClearContents on error goto 0 Cells(lastrow + 1, 1) = Cells(lastrow, 1) + 7 Rows(lastrow + 1).SpecialCells(xlCellTypeBlanks).Item(1).Activate End SubThis example will insert a row below the cell double-clicked on, copying the formulas from the row double-clicked on into the inserted row, then will remove constants from the inserted row based on the intersection range specified.Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'David McRitchie, 2007-09-07 insrtrow.htm on double-click '-- will copy more often than Extend Formulas and Format (tools option) Cancel = True Target.EntireRow.Copy Cells(Target.Row + 1, 1).EntireRow.Insert Cells(Target.Row + 1, 1).EntireRow.Select ActiveSheet.Paste Application.CutCopyMode = False On Error Resume Next '-- customize range for what cells constants can be removed -- Intersect(Selection, Range("b:IV")).SpecialCells(xlConstants).ClearContents On Error GoTo 0 End SubExample of an Event Selection Macro (#copyformulas). that will copy formulas from the preceding row when the cell in Column A is selected and there is no content below in column A and no previous content on the row. Extend Formats and Formulas can be fussy, this will trigger on the above conditions copying all formatting, and removing constants after copying the row above.Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'David McRitchie, 2004-06-02 '-- will copy more often than Extend Formulas and Format (tools option) Dim lastrow As Long If Target.Column <> 1 Then Exit Sub If Target.Row < 2 Then Exit Sub If Not IsEmpty(Target) Then Exit Sub 'cell must be empty lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row If Target.Row <> lastrow + 1 Then Exit Sub 'Row must be empty Rows(Target.Row - 1).Copy ActiveSheet.Paste Application.CutCopyMode = False on error resume next Target.EntireRow.SpecialCells(xlConstants).ClearContents Application.EnableEvents = False 'should be part of Change macro Target.Select Application.EnableEvents = True 'should be part of Change macro End SubAnother Example User, 2004-05-10, did not want to reinsert if already done.
Checking will be from current row and from next row in a separate macro that calls InsertRowsAndFillFormulas. Didn’t need test for .HasFormulaPrivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) '-- Code example to prevent reinserting if the current row '-- or next row row has Column A cel empty (testing with TRIM). Dim x As Long Cancel = True 'turn off Edit, mode applicable to "Edit directly in a cell" x = ActiveSheet.UsedRange.Rows.Count 'may alleviate some lastcell problems Target.Offset(1, 0).EntireRow.Interior.ColorIndex = xlNone '--Testin If Trim(ActiveCell.Offset(0, 1 - ActiveCell.Column)) = "" _ Or Trim(ActiveCell.Offset(1, 1 - ActiveCell.Column)) = "" Then MsgBox "skipping due to formula in column C and nothing in Column A --testing" Exit Sub End If Application.Run "personal.xls!InsertRowsAndFillFormulas", 1 Target.Offset(1, 0).EntireRow.Interior.ColorIndex = 36 '--Testing End Sub
The following event macro might be used to insert a single row before the a Subtotal Row, by double-clicking on the subtotal row. The content will be copied down into the inserted row, and the inserted row would have it's constants removed. This is a macro for the specific worksheet so it is easy to have it only remove content out of particular columns using Intersect, if you don't want to just remove constants.Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'D.McRitchie, 2007-05-11, not posted Cancel = True Target.Offset(0).EntireRow.Insert Target.Offset(-2).EntireRow.Copy Target.Offset(-2).EntireRow On Error Resume Next Target.Offset(-1).EntireRow.SpecialCells(xlConstants).ClearContents On Error GoTo 0 End Sub
For those that want something automatic, but will be inserting their own rows this might work for them. The following is a Change Event macro. It will fill in the formulas from the row above when entering a constant (text or number) into Column A provided the remainder of the row is empty. This should work similar to the InsertRowsAndFillFormulas macro at the top, except that you will have to use the Insert menu to insert rows and the formulas will not be filled in until something is entered in column A. One caveat, if anything is entered into a cell before entering into column A on the row this macro will not be filling in formulas, though the Extend formulas and formats will sometimes fill in the formulas as wanted.
Remember Event macros are installed differently from standard macros.Private Sub Worksheet_Change(ByVal Target As Range) 'David McRitchie, 2005-05-03 insrtrow.htm '-- will copy more often than Extend Formulas and Format (tools option) Dim lastrow As Long, rng As Range If Target.Column <> 1 Then Exit Sub If Target.row < 2 Then Exit Sub If IsEmpty(Target) Then Exit Sub Set rng = Intersect(Target.EntireRow, Range("B:IV")) If Application.Count(rng) > 0 Then Exit Sub rng.Offset(-1).Copy rng.Select On Error GoTo abort_sub Application.EnableEvents = False 'should be part of Change macro ActiveSheet.Paste Application.CutCopyMode = False rng.SpecialCells(xlConstants).ClearContents abort_sub: Application.EnableEvents = True 'should be part of Change macro End Sub
Simpler than InsertRowsAndFillFormulas, just insert a number of empty rows after each existing row (#InsertBlankRows)
Sometimes too many bells and whistles are added that make it hard to see what is actually happening. The following simply inserts empty rows and nothing else.Sub InsertBlankRows() '-- Ken Wright, 2003-08-09 Application.ScreenUpdating = False Dim numRows As Long Dim r As Long Dim Rng As Range Dim lastrw As Long numRows = InputBox("How many Rows") 'Note: cells(r,1) is same as cells(r,"A") lastrw = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A")) For r = Rng.Rows.Count To 1 Step -1 ' (r+1) to insert AFTER, (r) to insert BEFOE Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert Next r Application.ScreenUpdating = True End SubInsert one blank row before the last cell in Column A with content (not empty)Sub InsertBlankRowBeforeLast() Cells(Rows.Count, "A").End(xlUp).EntireRow.Insert End Sub
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 Guarantee2RowsAfterA_values() Dim rng As Range, i As Long Set rng = Intersect(Columns("A:A"), ActiveSheet.UsedRange) For i = rng.Cells.Count - 1 To 1 Step -1 If Trim(rng(i).Value) <> "" Then If Trim(rng(i + 1)) <> "" Then rng.Item(i).Offset(1, 0).Resize(2).EntireRow.Insert ElseIf Trim(rng(i + 2)) <> "" Then rng.Item(i).Offset(1, 0).EntireRow.Insert End If End If Next i End Sub Sub Guarantee3RowsAfterA_values() Dim rng As Range, i As Long Set rng = Intersect(Columns("A:A"), ActiveSheet.UsedRange) For i = rng.Cells.Count - 1 To 1 Step -1 If Trim(rng(i).Value) <> "" Then If Trim(rng(i + 1)) <> "" Then rng.Item(i).Offset(1, 0).Resize(3).EntireRow.Insert ElseIf Trim(rng(i + 2)) <> "" Then rng.Item(i).Offset(1, 0).Resize(2).EntireRow.Insert ElseIf Trim(rng(i + 3)) <> "" Then rng.Item(i).Offset(1, 0).EntireRow.Insert End If End If Next i 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 SubCode for inserting 3 up to 3 blank rows can be found on the code that corresponds with this web page.
Code to insert rows within a selection up to a specified quota of blank rows between populated rows (based on Column A) can be found in a reply based on Sean Bartleet's code in same thread (programming, 2005-10-20), it will be easier to read code here but check thread for comments.
The macro, Insert_Rows_between_existing, can be seen on the code/insrtrow.txt page and has a couple of minor corrections since posted, but the main points of what the code does and usage are summarized as follows:
- cell in column A is checked to see if it has content or not
- the cancel button on inputbox will actually cancel
- suggested default number of rows requires change to appication.inputbox
- only insert rows up to the quota request, be able to rerun without inserting any blank rows since quotas have already been met. (per my preference)
- change the msgbox, to reflect changes to the macro per my preference.
- show the selection range originally requested including inserted rows
- you can actually select the entire sheet now without performance penalty by restricting to the intersection of the selected rows and the usedrange
- Rows may be inserted below the requested range to fulfill quota witin the selection range (last row within range with content in col A)
- lines have been shortened with continuation to help with posting of code.
Select the nth and nth+1 columns of the range “whatever” on the active sheet (though you probably shouldn’t be selecting them, but just doing whatever you want to do with respect to them) -- Alan Beban. Range("whatever").Columns(n).Resize(,2).Select -- example to resize columns
Range("whatever").Rows(r).Resize(2).Select -- example to resize rows
With a column already selected, also select the column to the left or to the right, examples by Tom Ogilvy
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.
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.
'---- 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 on error resume next Intersect(new_area, multi_range).ClearContents on error goto 0To retain constants, code as formulas: i.e. ="abc"
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 -- right-click 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 On Error goto ErrHandler Application.EnableEvents = False 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"","""")" If 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 ErrHandler: Application.EnableEvents = True End Sub
Related to automatically entering formulas, this may be useful for some applications. I found the option very annoying.
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
Icons on the Worksheet in Excel 2002, introduces an icon that appears next to inserted cells when formatting of insertion does not match.
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 i = ActiveSheet.UsedRange.Rows.Count 'attempt to fix last cell location 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 SubInsert1or2RowsBeforeNonBlankA() 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 rowSub InsertALTrowsInSelection() 'David McRitchie, misc 2001-06-30 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim i As Long 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 SubAn 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:
also see Coloring within Ranges.
The comparison using .Text instead of .Value allow mixtures with #N/A error cells without additonal checking. Text is what is seen and includes formatting.Sub InsertRow_A_Chg() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 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).Text '// rows are inserted by looping from bottom For i = irow To 2 Step -1 If Cells(i, 1).Text = "" Then vcurrent = Cells(i - 1, 1) ElseIf Cells(i, 1).Text <> vcurrent Then vcurrent = Cells(i, 1).Text Rows(i + 1).Insert End If Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
This macro includes turning off screen updating and calculation to make this run quickly. The macro can be rerun without reinserting empty rows because a check is made of the cell above before inserting a row. Test for SpecialCells reduces the focus to the used range, and xltextvalues specifically restricts to cells with a constant value (not empty, not a formula). Longs are dimensioned (DIM) as Long for speed and more important because 65,536 rows can be in spreadsheet and integer is limited to 32,768.Option Explicit Sub InsertBefore_a_in_ColumnA() Dim cell As Range, rng As Range Dim i As Long, iCnt As Long Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues) If rng Is Nothing Then Exit Sub Application.ScreenUpdating = False Application.Calculation = xlCalculationManual iCnt = rng.Count For i = iCnt To 1 Step -1 If rng.Item(i).Row = 1 Then GoTo done If Trim(LCase(rng.Item(i))) = "a" Then If Not IsEmpty(rng.Item(i).Offset(-1, 0)) Then rng.Item(i).EntireRow.Insert Shift:=xlDown End If End If Next i done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
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.
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)
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.
For more on shortcuts see my shortcut pages shortx2k, and shortx95 for additional information.
XL2000 Keyboard Shortcuts Keystrokes to invoke Menu Rt.Click Menu Insert blank cells CTRL+SHIFT+PLUS SIGN Insert --> Rows insert rows Fill down CTRL+D Edit--> Fill--> Down N/A
There are also Mouse Shortcuts , largely undocumented in HELP, Use of Mouse and keys to move, and insert rows or columns.
To insert multiple lines, first select a range of the number of rows you want to insert, then use shortcut, or Insert menu to insert the rows before the selection. (note: INSRTROW macro inserts rows with formulas down from the active cell row).
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 inadvertent duplicate entries.
To preserve some constants and information from the above removal, don't include those columns in your selection, or you can code as formulas:
- 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).
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.
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 nextThe 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 97Delete All Rows that are completely emptySub 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 SubWithin 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
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. (also see #fixrightminus)=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, CR 2002-10-29 ' CDBL 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(Replace(cell.Value, "CR", "-")) 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 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
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() 'email@example.com, misc, 2000-11-22 <uFwUNysUAHA.244@cppssbbsa05> ' 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)
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 -- right-click 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.
If you have text in a cell in your Debit or Credit column instead of a number or an empty cell you will get #VALUE! errors in your balance column. You can use Edit, Goto, Special Values, text to identify problem, or you can use Conditional Formatting so you can spot problems as they occur later on. While doing Conditional Formatting might also set up a second formula to catch lack of a formula in your balance column (i.e. an Empty cell)
Using the examples on this page you see you have Debits in column E, your Credits in Column F, and your Balance in Column G. Your Conditional Formatting formula is based on your active cell, the cells that can be colored is based on your selection.
Cells containing Text in columns E and F would be invalid, to check for use ...
Select columns E:F with cell E1 as the active cell
Format, Conditional Formatting,
Formula is: =AND(ROW()<>1,ISTEXT(E1))
format button, patterns, choose a color (orange), OK, OK
To check for a break in your balance column as in a missing formula by testing for numeric, use ...
Select column G then with G1 as the active cell.
Format, Conditional Formatting,
Formula is: =AND(ROW()<>1,NOT(ISNUMBER(G1)))
format button, patterns, choose a different color (turquoise), OK, OK
You should see very easily now why you have #VALUE! errors because you have text values. The second color will indicate where you have an empty row and your balances start over rather than continue.
E F G H 1 Debit Credit Balance formula 2 500 500 500 3 2 502 =OFFSET(G3,-1,0)+E3-F3 4 40 462 =OFFSET(G4,-1,0)+E4-F4 5 a #VALUE! =OFFSET(G5,-1,0)+E5-F5 6 40 #VALUE! =OFFSET(G6,-1,0)+E6-F6 7 10 (space) #VALUE! =OFFSET(G7,-1,0)+E7-F7 8 20 9 1 1 =OFFSET(G9,-1,0)+E9-F9 10 30 -29 =OFFSET(G10,-1,0)+E10-F10 11 D B #VALUE! =OFFSET(G11,-1,0)+E11-F11 12 A H #VALUE! =OFFSET(G12,-1,0)+E12-F12 13 14
More information on Conditional Formatting, debugging what type of data you have, and the reason why you don’t use SUM in your checkbook balances.
A rather novel approach posted by Don Guillet, 2004-03-08, this will find the last number on sheet2 in col F. The trick is to look for a large number that can’t exist. Just make sure that your number is not larger than your criteria. The last number is selected even if nonnumbers appear below.
- Excel 2003, popup when inserting a row, Tools/Options/Edit/... Remove checkmark on Show Insert Option Buttons.
=SUM(A2:OFFSET(A300,-1,0)in cell A300.
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2006, F. David McRitchie, All Rights Reserved