Automatically Run Code to Insert New Row

**floppygoat** · 02-03-2016, 10:03 AM

Hi Guys,

I have been searching tirelessly for a clear way to create a code which I can apply to my current worksheet which will AUTOMATICALLY create, or delete, a new row once a previous row is filled with new information, or cleared of existing information.

This is necessary because I have a worksheet which serves as a meeting agenda with several topical "sections" of information e.g. "Change Orders," "Revisions," "Requests for Information," "Safety," etc. As you can imagine, the information, delineated as individual line items, associated with each section is bound to change from agenda to agenda. Therefore, to accommodate this "dynamic" range of information, it is necessary to add or delete the appropriate numbers of rows per section per week. This not only gets repetitious, but this worksheet is intended for use among my colleagues and I honestly cannot expect them to know how to add and delete rows manually.

I capitalized automatically above because I do not want to manually run a macro. Instead, I want the code to automatically recognize the changes and make the appropriate adjustment.

As it stands, the sections are not tables but cant be converted to tables if necessary.

I have attached the worksheet for reference.

P683-15 _ Agenda Body _ 003.pdf

P683-15 _ Agenda Body _ 003.xlsx

Thanks for your help,

TW

**joe31623** · 02-03-2016, 12:36 PM

Floppy (it's fun to say),

It looks like:

A1: you want to insert an additional row anytime you type something in (any column of) the last available row between sections A & B, B & C, C & D, ... -- correct?
A2: you want only one blank row between sections A & B, B & C, C & D, ... -- correct?

If both of my assumptions (A1 & A2) above are correct, I propose the following solution:

S1a: Any time ANY text is written in any column of the row that is the last available (blank) row between sections A, B, C, ...: insert another blank row.
S1b: Any time the document is saved, closed, or opened, run a loop through the document, deleting all blank rows just before sections B, C, D, ... begin except the last blank row (if there are multiple blank rows).

Please verify my assumptions: A1 & A2 are correct and comment on my proposed solution: S1a & S1b.

**floppygoat** · 02-03-2016, 08:02 PM

Joe,

That is correct.

However, after thinking more about it, I have come to the following conclusions:

1. I am less concerned with too many blank rows, since blank rows are easier to delete than create, and since you may want more than one blank row to force a section to begin on a new page. Therefore, this part of the request (A2) can be disregarded. However, an option to add such a code if desired wouldn't hurt

2. It is equally important to create a new row before the first line item each time new information is added in the "first" row of any section since new information is often added reverse chronological order. Therefore, I would like to maintain at least one blank row between the section header and the first line item of that section and at least one blank row between the last line item of that section and the next section header.

Consider the attached spreadsheet for the following examples:

1. If new information is added to row 11 (the row immediately below the safety section header), a new row would be inserted, maintaining a blank row at row 11, and pushing the just added information down to now row 12. This would be the same for each section.
2. Similarly, if new information is added to row 17 (the last row of the safety section and the row immediately before the submittal section header), a new row would be inserted, maintaining a blank row at now row 18.
3. This of course would have to work for each section and regardless of how many line items were inserted or whether they were inserted at the end or beginning of the section.

If you have any additinal question, please let me know.

I am grateful for your interest.

Thanks,

TW

Automatically Run Code to Insert New Row

LinkBack

Thread Tools

Rate This Thread

Display

Automatically Run Code to Insert New Row

Re: Automatically Run Code to Insert New Row

Re: Automatically Run Code to Insert New Row

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How can I automatically insert this source code into new tabs or all Tabs?

[SOLVED] Excel 2010 - VBA code to automatically insert username into cell on worksheet

Amendment to code: automatically insert rows if needed

[SOLVED] formula to insert code and description automatically from another database based on ref

[SOLVED] Automatically Insert Rows, slight change in code required

Code to automatically insert space in a string

[SOLVED] automatically insert town name after entering zip code

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions