Insert X amount of blank rows after duplicates or unique record is found

[DBjjprint]

I need a macro to insert blank rows after a unique record or duplicates, total of 8

Example:

If a unique record is found then insert 7 blank rows

If 3 duplicates are found then insert 5 blank rows

If 10 duplicates are found, keep the first block of 8 and then insert 6 blank rows

Ect..

 

[Joe4]

Welcome to the Board!

How many/what columns are being used in determining if they are duplicates?
Are all the duplicate rows always above/below one another without any other rows in between?

In cases like this, often times showing a simple example of your data and expected results is very helpful.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[DBjjprint]

One column is used in determining if they are duplicates
Yes, duplicate rows always above/below one another without any other rows in between
This is the sample data

multitest.xlsx
	A	B	C	D	E
1	traymark_	sequence	cont_id	endorse	first
2					john
3					john
4					Bob
5					Mary
6					Mary
7					Mary
8					Mary
9					Mary
10					David
11					David
12					Cindy
13					Cindy
14					Cindy
15					Cindy
16					Cindy
17					Cindy
18					Cindy
19					Cindy
20					Cindy
21					Cindy
22					Don
23					Don
Data

This is the results needed (without the 1-8 in the sequence column)

multitest.xlsx
	A	B	C	D	E
1	traymark_	sequence	cont_id	endorse	first
2		1			john
3		2			john
4		3
5		4
6		5
7		6
8		7
9		8
10		1			Bob
11		2
12		3
13		4
14		5
15		6
16		7
17		8
18		1			Mary
19		2			Mary
20		3			Mary
21		4			Mary
22		5			Mary
23		6
24		7
25		8
26		1			David
27		2			David
28		3
29		4
30		5
31		6
32		7
33		8
34		1			Cindy
35		2			Cindy
36		3			Cindy
37		4			Cindy
38		5			Cindy
39		6			Cindy
40		7			Cindy
41		8			Cindy
42		1			Cindy
43		2			Cindy
44		3
45		4
46		5
47		6
48		7
49		8
50		1			Don
51		2			Don
Results

 

[Joe4]

Kind of a rather brute force VBA method, but it seems to do what you want/show:

Sub MyInsertRows()

    Dim r As Long
    Dim ct As Long
    Dim n As Long
    
    Application.ScreenUpdating = False
    
'   Initialize row to start on and count (skip first row of data)
    r = 3
    ct = 1
    
'   Loop through all rows until you find a blank (bottom)
    Do Until Cells(r, "E") = ""
'       Check to see if value in column D matches row above
        If Cells(r, "E") = Cells(r - 1, "E") Then
'           If equal add one to counter
            ct = ct + 1
'           Increment row counter
            r = r + 1
        Else
'           If not, calculate number of rows to insert
            n = 8 - (ct Mod 8)
'           Make it is not an exact multiple of 8
            If n < 8 Then
'               Insert rows
                Rows(r & ":" & r + n - 1).Insert
'               Increment row counter
                r = r + n + 1
'               Reset counter
                ct = 1
            Else
'               Reset counter
                ct = 1
'               Increment row counter
                r = r + 1
            End If
        End If
    Loop
    
    
    Application.ScreenUpdating = True
    
End Sub

 

[DBjjprint]

This works perfect, Thank you

 

[Joe4]

You are welcome.
Glad I was able to help!