Automatically insert rows at varied intervals

[Peskarik]

Hello,
I usually never use VBA or Excel, doing everything in R, as
I am econometrician. :whistle:
But this time I have to automate another person's Excel file that contains lots
of info, formulas, and plots.
The problem I have is following:
I have a sheet with an array of say (1000x20), the 20 columns
are named. Some of these columns contain data that never changes,
some contain data that must be filled in by hand, some contain
formulas that take data from other columns in the array.
Array is organized as follows:
one can see it as a collection of smaller arrays of (36x20). Sometimes
it can be (34x20) or (35x20), that is, number of rows vary.
Column NAME contains names of the companies, column DATE contains
date formatted like 01.01.1998. There is column ID, which contains
numeric ID for each company. Then there are columns that contain
formulas etc.
The smaller arrays are stacked, which means each array is for one
particular company, so in column NAME there are 36 occurencies
of one particular name, then 36 occurencies of another and co on.
Same goes for column ID. In column DATE there are 36 past months for each company.
The formula columns take data from other columns for each particular
firm.
WHAT I NEED TO DO EFFICIENTLY:
I need to write a piece of VBA code that inserts a new row
at the bottom of each smaller array (that is the row for
the new month), then fill in the cells in the row accordingly
That is copy in the name and the ID of the company
in columns NAME and ID, the new date (say 01.09.2008) in the DATE
column, and copy formulas from the row above.
I have done this with Do Until Loop, If statement, and using Offset
function a lot. My code finds column NAME and then it compares
each cell value in that column going down with the previous cell value,
and when they differ I know the array for the next company starts
and I insert new row, then Autofill date (using offset), copy name
and ID from cells above, copy formulas.
This is very slow!!! :banghead: :dunno
Anybody can suggest another, faster way to do these operations,
maybe counting similar IDs, and jumping to the last row containing
each particular ID, inserting rows, copying formulas "in bulk", etc
Really need some help, people.:help
Thank you in advance!
Regards,
Sergey

[Bob Phillips]

Have you turned screenupdating off and set claculation to manual before running the loop?

[Peskarik]

Have you turned screenupdating off and set claculation to manual before running the loop?

Hi,

No, I haven't done these things.
I know how to turn screen updating off in VBA.
But what "set calculation to manual" means and how can I turn it on? :think:

For some reason the code runs slowly, I do not use any .Select methods but I do use a fair bit of .Offset() functions. Could this also be a reason for slowdown? Looking at the screen and the places where rows get inserted, the speed is like 1 second per 2 inserted and filled rows. :fainted:

Thank you for your help!

[Bob Phillips]

Calculation is unset with



Application.Calculation = xlCalculationManual


and reset with


Application.Calculation = xlCalculationAutomatic


could make a big difference if you have many formuale.

Offset per se is not a problem, not like Select, but it depends upon the code, the number of iterations, etc.

[Bob Phillips]

You said 1-2 secs per insert, this definitely sounds like calculation. Do you see Calculate in the status bar at that point?

[Peskarik]

You said 1-2 secs per insert, this definitely sounds like calculation. Do you see Calculate in the status bar at that point?

xld, let me see what is happening (I am changing the code a little in hope it runs faster) and then I post here again. Hopefully, you'll browse this thread a little later.

Thanks for your help!!! :bow:

[Peskarik]

xld, your suggestions speeded up everything quite substantially! Thanks! :beerchug:

In my loop, when I ran in debug mod line by line, I have noticed that the second line in the code snippet below is especially slow:



' next line selects a range across all the rows in the array
Set longrng = Range(rng3.End(xlToLeft), rng3.End(xlToLeft).End(xlToRight))
' next line inserts a new row by shifting cells down
longrng.Insert Shift:=xlDown


But I guess there is no optimization for that line, so to speak.

[Bob Phillips]

That is very odd code. What exactly do YOU think that code is doing?

[Peskarik]

That is very odd code. What exactly do YOU think that code is doing?

The problem I had is to insert a row.
The code specifics are such that before the above code snippet I come to a cell which is, say, in the middle of my array (example: array is 1000x20, and the cell is on 456 row and in 12 column). Call that 1000x20 array - array1.
Moreover, to the left of the array plus some empty columns, there is another array (array2), which I am not allowed to split by simply inserting a new row across all the columns.

Therefore, I go from the cell all the way to the left, then I select all the columns in array1 and insert an empty row only in this array by shifting cells down.

That is what this code is doing. :guitar2: