Insert row and drag/copy above formulas

nicolewh · Aug 8, 2009

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CBen%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CBen%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CBen%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> I have a cell named “inserter” in column A. Whenever the cell two-above-and-over is no longer blank (filled) I want macro to insert a new row right above the “inserter” cell’s row (below the recent filled cell), and copy/drag into the new row any formulas above the new row. This way I don’t have to insert a new row every time I want to add to the list in column B.

This code is kind of what I was looking for but it doesn’t copy/drag the above-row formulas into new row. Also, it is triggered by formulas even when they have blank values (“”). If the reference cell is blank (“”) I don’t want it to be triggered.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Cells(Target.Row, 2).Offset(1, -1) = "Inserter" Then
Rows(Target.Row + 1).Insert
Cells(Target.Row + 1, 1).Select
End If
End Sub

Thank you
Nicole

jbeaucaire · Aug 8, 2009

I think you're missing one of the key features in Excel, the "List" feature. For now, move that INSERTER row of yours way down out of the way so your current dataset is clean and stands alone.

Now with your activecell in the dataset somewhere, press CTRL-L and Excel will offer to make your current region of data into a "List". Verify the range is correct and whether you have headers or not (you should!), then click OK.

Excel has now defined this range as a set of data and considers the formulas and formatting within the "List" to be important.

You'll notice when your activecell is in the list, Excel now offers you a blank line with a * at the bottom of the list. If you put data in ANY cell in that row, Excel will immediately expand the list to encompass that row permanently and copy down any formulas and formatting from above, just like you want.

No macro needed.

Another benefit of the "List" feature is the built-in autofilter in the header row, very handy.

nicolewh · Aug 8, 2009

I have tried this feature but I have different list just below this first list that I want to always keep below the first list. So whenever I add to the first list I need everything below to shift downward.

jbeaucaire · Aug 8, 2009

Make sure there is at least one blank row between the two sets of data before you activate the list and it will keep them separate.

Insert row and drag/copy above formulas

nicolewh

Well-known Member

Excel Facts

jbeaucaire

Well-known Member

nicolewh

Well-known Member

jbeaucaire

Well-known Member

Similar threads

Forum statistics

Share this page

Insert row and drag/copy above formulas

nicolewh

Well-known Member

Excel Facts

jbeaucaire

Well-known Member

nicolewh

Well-known Member

jbeaucaire

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock