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

DBjjprint

New Member
Joined
May 8, 2024
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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..
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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
ABCDE
1traymark_sequencecont_idendorsefirst
2john
3john
4Bob
5Mary
6Mary
7Mary
8Mary
9Mary
10David
11David
12Cindy
13Cindy
14Cindy
15Cindy
16Cindy
17Cindy
18Cindy
19Cindy
20Cindy
21Cindy
22Don
23Don
Data


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

multitest.xlsx
ABCDE
1traymark_sequencecont_idendorsefirst
21john
32john
43
54
65
76
87
98
101Bob
112
123
134
145
156
167
178
181Mary
192Mary
203Mary
214Mary
225Mary
236
247
258
261David
272David
283
294
305
316
327
338
341Cindy
352Cindy
363Cindy
374Cindy
385Cindy
396Cindy
407Cindy
418Cindy
421Cindy
432Cindy
443
454
465
476
487
498
501Don
512Don
Results
 
Upvote 0
Kind of a rather brute force VBA method, but it seems to do what you want/show:
VBA Code:
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
 
Upvote 1
Solution
You are welcome.
Glad I was able to help!
 
Upvote 1

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top