# How to insert rows dynamically with in group

Solved/Closed
alveeru - Dec 18, 2010 at 09:57 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 9, 2011 at 10:29 PM
Hi,
currently I am creating a macro, in to that I have data for row and columns. now I want to count the product in column A, with formula =counta (A: A).after taken product count, I want divide the product in to 4 group (like example below or sheet 1, 2 and 3 in attach sheet) for that I follow the formula =if (product count>30; product count/4; product count/3) (like shown in example below).after that I want to insert 3 rows after each group dynamically (see Sample data).

For better understanding I have attached sample date file as a Zip format.
In that file on sheet 2 and sheet 3 you will find the data which I have separate with in group based on total no of product.
ex : if total product is 25 then :
G1 =9
G2= 8
G3 =8
G4 =NA

ex : if total product is 28 then :
G1 =10
G2= 9
G3 =9
G4 =NA

ex : if total product is 30 then :
G1 =10
G2= 10
G3 =10
G4 =NA

ex : if total product is 33 then :
G1 =9
G2= 8
G3 =8
G4 =8

one more ex: if total product is 34 then:
G1 =9
G2= 9
G3 =8
G4 =8

one more ex : if total product is 35 then :
G1 =9
G2= 9
G3 =9
G4 =8

one more ex : if total product is 36 then :
G1 =9
G2= 9
G3 =9
G4 =9

one more ex : if total product is 37 then :
G1 =10
G2= 9
G3 =9
G4 =9

it will apply the same method for "n" of product.

Then it should sum within group (see sheet 1, 2 or 3 data), it should also show the no of product in each group (like in sample data).

I think it will help for better understanding,

How it can be done? Plz help me......

Thanks you very much for your support,

Virendra
Related:

## 1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 9, 2011 at 10:29 PM
see if this helps

```Sub doDataGroup()

Dim lCount           As Long       'total items to be grouped
Dim iMaxSheetItem    As Integer    'max items on one sheet
Dim iSheetNeed       As Integer    'calculated number of sheets needed
Dim iCounter         As Integer    'counter to loop thru sheets
Dim iSheetItem       As Integer    'number of items of the sheet being processed
Dim iRemainder       As Integer

iMaxSheetItem = 10

lCount = 37 'example that there are 37 total items

iRemainder = lCount Mod iMaxSheetItem
iSheetNeed = ((lCount - iRemainder) / iMaxSheetItem) + IIf(iRemainder > 0, 1, 0)

For iCounter = iSheetNeed To 1 Step -1
iRemainder = lCount Mod iCounter
iSheetItem = ((lCount - iRemainder) / iCounter) + IIf(iRemainder  > 0, 1, 0)
lCount = lCount - iSheetItem

Debug.Print "Item on Sheet " & iCounter & ": " & iSheetItem

Next iCounter
End Sub```