Inserting lines into tables

I have a sheet with some 2,000 formatted lines and need to insert
another blank but formatted line together with all the formulae. I
currently copy the first blank line and then insert this into the sheet
before the last line of the table, shifting all following lines down.
This is the only way I can think of that will allow me to also keep the
number of the last row (which obviously changes) valid in various other
formulae.

This works OK but is TERRIBLY slow when doing it a couple of hundred
times. Any tips on how to speed up the process?

TIA, Dave

Dave,
Assumptions:
1. You have a data table with heading
2. One data record (one row in the table) is a mix of cells the user has to
fill and cells that have formulas.
3. You want to automate inserting an additional line anywhere in the table
between two existing rows, or to create a new record at the end of the
table, containing empty cells the user has to fill and formulas that are
identical for the columns they are in.

I could think of doing the folowing:

1. Record a macro with reference set as relative. You record the steps you
want to automate. (Sorry if you are familiar with this). The best is you
select a cell in colA before you start recording and you start the recording
with stepping one cell down. Then insert a row above the active cell and
copy+paste the content of the row above, then delete the content of the
user-entry cells in the new row. Then stop recording.
2. With Alt+F11 go into the VB Editor and have a look at the code you
recorded. You might want to optimise it. One improvement can be to add
Application.Screenupdating = False as first line of the macro. An other one
can be: If Activecell = Empty or Activecell.Row = 1 then End 'added as
second row.
3. At the heading of the sheet you insert either a CommandButton or an
Textbox with a caption: 'Insert new item below the active cell'. (With panes
frozen the heading and the button will always be visible.)
4. You assign the macro you recorded to this object or the CommandButton.

This one does not solve the issue of adjusting the running number in ColA in
case you have that, but for that there is another solution if you need it.

Gabor

You can create a macro that
"AussieDave" <[email protected]> az alábbiakat írta a következo
hírüzenetben: [email protected]...
>I have a sheet with some 2,000 formatted lines and need to insert
> another blank but formatted line together with all the formulae. I
> currently copy the first blank line and then insert this into the sheet
> before the last line of the table, shifting all following lines down.
> This is the only way I can think of that will allow me to also keep the
> number of the last row (which obviously changes) valid in various other
> formulae.
>
> This works OK but is TERRIBLY slow when doing it a couple of hundred
> times. Any tips on how to speed up the process?
>
> TIA, Dave
>

Thanks for your time Gabor. I'm familiar with the techniques you
described and already use them. However, my current problem is
automatically adding hundreds of records from a CSV file to the bottom
of my table, taking up formats and formulae from the preceding lines.
I also have other formulae which SUMIF using the first to last line of
the table and the last line needs to dynamically increase as I add
lines. I already use the solution you suggested but it is VERY slow
when adding hundreds of records.
Again, thanks for your time - Dave

In case anyone is interested, I figured out the answer.
I made Calculation "Manual" at the start of the macro and went back to
"Automatic" at the end - Voila!!!

Dave, I have no good idea other than to try to come up with a formula that
does not have to be overwritten every time you enter a new data record. By
this Excel would not spend time on re-doing all existing cells' formulas.
But it will still spend time on re-calculating the results. In this case of
course you wouldn't have to insert new lines but fill content into existing
cells only. That might speed up the process.

I have experience with huge data table being processed over hours and hours
by a macro just because the macro inserted lines into the table. As soon as
I could eliminate the insertion step the speed icreased significantly.

Gabor

"AussieDave" <[email protected]> az alábbiakat írta a következo
hírüzenetben: [email protected]...
> Thanks for your time Gabor. I'm familiar with the techniques you
> described and already use them. However, my current problem is
> automatically adding hundreds of records from a CSV file to the bottom
> of my table, taking up formats and formulae from the preceding lines.
> I also have other formulae which SUMIF using the first to last line of
> the table and the last line needs to dynamically increase as I add
> lines. I already use the solution you suggested but it is VERY slow
> when adding hundreds of records.
> Again, thanks for your time - Dave
>