Automatically insert a line

[aeddave]

Hi,

Is there a function or formula to automatically insert a new line in Excel.

In this instance, I have a spreadsheet with three sections (top to bottom).
As a new line (record) is entered, I need a new line (with the same
formatting / formulas) to be created below to enter another record if needed.

The effect would be to increase the size of that section by one line witout
running into the next section.

Thanks

 

[JLatham]

There is no worksheet function to either add/insert or delete a row/column on
a worksheet. This type of thing has to be done with VBA (a macro). One
could be tied to a particular column in the Worksheet_Change() event handler
to do this - gets a bit complex in copying the format and formulas, but still
doable.

 

[Rick Rothstein]

Something like this VBA event code should work (I think<g>). As you said,
the test must be tied to a column that you know something about. In my
example below, I am testing the ColorIndex in Column H (but the test could
just as easily test a font's boldness, a formula, or any other condition
that is fixed in a particular column.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Offset(1).Columns("H").Interior.ColorIndex _
<> .Columns("H").Interior.ColorIndex Then
Application.EnableEvents = False
.EntireRow.Copy .Offset(1)
.Offset(1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End If
End With
End Sub

To initiate its use for the very first time, one would just edit **any**
constant data item in the last row back to itself... doing that will
"condition" the blank row under it... after that, filling in any data in the
conditioned last (blank) row will automatically condition the row under it
for use when the next data row is filled in.

For the OP... if you aren't sure how to implement the above code, do this...
right click on the tab at the bottom of the worksheet that is to have this
functionality, select View Code from the popup menu that appears and then
copy/paste the above code into the code window that appears. That is it. Now
go back to the worksheet and do the initialization step I mentioned in the
above paragraph (you only need to do this one time for the worksheet;
everything will work automatically after that). If you need help in
establishing the actual test condition that I mentioned in the first
paragraph, post back with a description of at least one of your columns
(tell us which column it is and what is "special" about it; that is, does it
contain a formula? does it use a certain font? is the font styled in anyway?
etc.).

 

[JLatham]

I did some initial 'testing' in which I tested a cell in a particular column
for NOT ISEMPTY() along with testing the cell immediately below it for
ISEMPTY() and then inserted the row. I didn't take it beyond that to copy
any existing formulas or formats from the current row into the new one -
you've taken care of that.

 

[Rick Rothstein]

I think I would rather leave it tied to a format setting of some kind...
with your method, if the cell in the column you chose to monitor is not
filled in right away (that is, other cells on the row are filled in before
it), the Change event code will execute over and over again, once for each
data entry not in the monitored column... when a format condition is copied,
it "sticks" whether its cell has data filled into it or not, so the
monitored format property act like a Boolean switch (it is either there or
it is not).

 

[JLatham]

I can't argue with that at all, it was actually one of the stumbling blocks I
thought about. Trying to add intelligence to code in a situation like this
can get real sticky and could even impose some restrictions on the user that
aren't realistic for real-world use. And what you've mentioned is exactly
that kind of deal (I mean the possible 'failure' of my method).