Conditional insertion of rows and cell values

[kenshin_fan1]

Hi all,
I am not too advanced with VBA programming but i have been trying to find and alter codes that i can find to suit my purposes. It has worked well so far but i got a problem and it has me stumped. Basically i need to work through ascending interval (from and to) data.

Now if the difference within the interval is greater than 3 then i need to insert an entire row with an interval which is the maximum difference is 3. I have to keep doing this until the interval difference is less than 3. i.e if the interval is From 10 to 20, the difference between them is 10 which means i have 3 intervals of 3 and 1 interval of 1. This i have to insert 4 entire rows with the new interval From 10 to 13, 13 to 16, 16 to 19, 19 to 20.

An example is below

From to
12 16
16 18
18 19
19 20
20 28
28 30
30 40

From to
12 15
15 16
16 18
18 19
19 20
20 23
23 26
26 28
28 30
30 33
33 36
36 39
39 40

I have tried using functions in the excel sheets but it is getting too complicated for my level to piece all of it into a macro. Any help would be much appreciated.

[mrice]

This macro should help.

Please Login or Register  to view this content.

It keeps things relatively simple by working from the bottom of the list upwards and therefore avoiding having to recalculate positions in the list as rows are added.

[DonkeyOte]

mrice,

not sure if you will find this info useful or not but this:

Cells(Rows.Count,1)

would be preferable to

Cells(65536,1)

given that long term users may move from pre 07 to 07 versions and the former approach will not cover row range of 07 --> Rows.Count on the other hand is compatible for both versions.

Also regards use of Integer as data type for Row variables - this is very risky given Integer boundaries versus Row boundaries. I've also learnt myself recently that in 32 Bit VBA the Integer type is converted to Long automatically so in reality there is no gain to be had from using Integer and simultaneously no reason not to use Long type (other than using Integer as a sanity check for generating overflow for unexpectedly large variable values)

Just my $0.02

Cheers

[shg]

Try this. Assumes the data starts in A1.

Please Login or Register  to view this content.