VBA insert/delete rows to accommodate exact number of rows from incoming data

Hi,

I am a newbie here and just started working on basic excel vba. I have an issue about adjusting rows automatically.

Situation: I have 4 sheets nameld Sheet1,Sheet2,Sheet3 and Sheet4. Sheet1,2,3 with datas on Column A and B (List of Students) for Boys and Girls respectively.
Sheet4 has datas within named ranges "Class1" and "Class2" lined up in Column B. The list of boys and girls datas from Sheet1/2/3 are dynamic with rows of datas range from 10 to +50 students.

Problem: Adjusting the rows automatically is the problem. Example, if the list of students from Boys has 10 rows while "Class1" has 20 rows then "Class1" should be trimmed automatically to 10 rows to accommodate the datas exactly. This goes the same with Class2. Auto insert or delete of rows sort of.

Ideally, Dynamic ranges applies but with one named range in one column only. I have two named ranges lined up in Sheet4 Col B with other datas in between and this is where the issue start.

This is the code i have so far, it works with copying and filling. I dont have the vba for auto adjustment of rows whether insert/delete based on the incoming datas from Sheet1,2 or 3.

Please Login or Register  to view this content.

Note: "ClassB" and "ClassG" are just the names of of the 1st Cells in both named ranges "Class1" and "Class2".

Sorry for the long story, I hoped i did not confused you so much.

Please help me! Tks
Iloco

Hi, iloc,

I have two named ranges lined up in Sheet4 Col B with other datas in between and this is where the issue start.

It might be a good idea to give us a look at your workbook with an example of the wanted output.

Ciao,
Holger

Hi, Iloc,

regarding your question about the filling for teh rows I would suggest to wotk with ranges for the maximum of boys and girls and hide the rows which will not be needed. To get there I changed the numbers (only on Sheet "V - Dandelion") to show the maxiumum of entries for either boys or girls.

The AutoFill for Boys on "English V - Dandelion" could then be written like this:

Please Login or Register  to view this content.

I exported most of the forms (I will have to look at thme later on) as well deleted a lot of the sheets (for reason of the file size only) and left only UF5 (where the ranges are to be selected as example).

Please have a look at the workbook and see if this may help with the problem.

Ciao,
Holger