Hi forum gurus,

I'm a novice at VBA, having started to learn it on Friday! I have 50 spreadsheets formatted the same as I'm working on now, and I've been writing code to automate the process. There's one step I can't seem to find answers for on the net.

Essentially I needed to group data when the depth jumps (which I've managed no probs, see column B).

In the columns below, I have two examples, highlighted in column M. Group 1 has 7 cells in it, group 2 has 50 cells in it. Each group is separated by a blank row. The number of groups varies per spreadsheet, as does the length of each group. If the group is <40 continuous cells (e.g. Group 1), then it's to remain as is. If it's more than 40 cells (e.g. Group 2), split these in half by inserting a blank row after the midpoint. So, for group 2 which runs from M10-M59, insert a new row at M34 (or M35, not fussed).

To complicate things, if there are more than 80 cells, it would have to be subdivided into 3 equal sections; more than 120 cells into 4 sections etc. I've written a quick Mround formula which defines the number of segments each group would have to be divided into in column O. So Group 1 would need to be subdivided 0 times, Group 2 subdivided 1 time.

Any ideas? I'm really bogged down on this.

Thanks in advance, Hugo

<tbody>

</tbody>

I'm a novice at VBA, having started to learn it on Friday! I have 50 spreadsheets formatted the same as I'm working on now, and I've been writing code to automate the process. There's one step I can't seem to find answers for on the net.

Essentially I needed to group data when the depth jumps (which I've managed no probs, see column B).

**I also need to subdivide any groups more than 40 cells in a row into equal portions**- I can't figure this bit out.In the columns below, I have two examples, highlighted in column M. Group 1 has 7 cells in it, group 2 has 50 cells in it. Each group is separated by a blank row. The number of groups varies per spreadsheet, as does the length of each group. If the group is <40 continuous cells (e.g. Group 1), then it's to remain as is. If it's more than 40 cells (e.g. Group 2), split these in half by inserting a blank row after the midpoint. So, for group 2 which runs from M10-M59, insert a new row at M34 (or M35, not fussed).

To complicate things, if there are more than 80 cells, it would have to be subdivided into 3 equal sections; more than 120 cells into 4 sections etc. I've written a quick Mround formula which defines the number of segments each group would have to be divided into in column O. So Group 1 would need to be subdivided 0 times, Group 2 subdivided 1 time.

Any ideas? I'm really bogged down on this.

Thanks in advance, Hugo

B | L | M | O | |

1 | DEPTH (M) | depth diff | Countgroup | Mround |

2 | 331.0128 | 0.1524 | 7 | 0 |

3 | 331.1652 | 0.1524 | 7 | 0 |

4 | 331.3176 | 0.1524 | 7 | 0 |

5 | 331.47 | 0.1524 | 7 | 0 |

6 | 331.6224 | 0.1524 | 7 | 0 |

7 | 331.7748 | 0.3048 | 7 | 0 |

8 | 332.0796 | 12.0396 | 7 | 0 |

9 | ||||

10 | 485.2416 | 0.1524 | 50 | 1 |

11 | 485.394 | 0.1524 | 50 | 1 |

12 | 485.5464 | 0.1524 | 50 | 1 |

13 | 485.6988 | 0.1524 | 50 | 1 |

14 | 485.8512 | 0.1524 | 50 | 1 |

15 | 486.0036 | 0.1524 | 50 | 1 |

16 | 486.156 | 0.1524 | 50 | 1 |

17 | 486.3084 | 0.1524 | 50 | 1 |

18 | 486.4608 | 0.1524 | 50 | 1 |

19 | 486.6132 | 0.1524 | 50 | 1 |

20 | 486.7656 | 0.1524 | 50 | 1 |

21 | 486.918 | 0.1524 | 50 | 1 |

22 | 487.0704 | 2.7432 | 50 | 1 |

23 | 489.8136 | 0.1524 | 50 | 1 |

24 | 489.966 | 0.1524 | 50 | 1 |

25 | 490.1184 | 0.1524 | 50 | 1 |

<tbody>

</tbody>

Last edited: