Insert Blank Row with data match change

M

Mitche

I'm trying to figure out a way to evenly space data. Where I don't have a
repeat value in the row directly below the current one I want to insert a
blank row. Some of my data series looks like this.

Column A Column B

Part Number Description
(Cell A2): AC011000 (Cell B2) FUEL TANK, CARTRIDGE ADAPTOR
(Cell A3: AC011000 (Cell B3) FUEL TANK, CARTRIDGE ADAPTOR
BLANK ROW
(Cell A5) AC011001 (Cell B5) XX55 Assy, Li-80 Adaptor
BLANK ROW
AC051000 KIT, XX25 MANUALS
AC051000 KIT, XX25 MANUALS
BLANK ROW

I want a blank row to be inserted below Row 5 because the part number is not
repeated.
Can anyone help?
 
D

Don Guillett

Not quite sure of your layout but try this

Sub insertrowifnondup()
Dim mc As Long
Dim i As Long
mc = 1 'col a
For i = Cells(Rows.Count, mc) _
.End(xlUp).Row To 2 Step -1
If Len(Application.Trim(Cells(i, mc))) > 0 And _
Application.CountIf(Columns(mc), _
Cells(i, mc)) < 2 Then Rows(i + 1).Insert
Next i
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I insert a row without the formula changing? 1
Combination of INDEX MATCH FREQ ROW INDIRECT - find unique values using2 conds with UIDs that may ha 0
Index and Match 5
How to do an IF statement that copy data based on a partial match 0
Create if partial match then copy and paste data from one cell to another 1
Auto insert a blank row above the current row based on a cell val. 2
Averageifs - criteria is cell is not blank 1
Insert a Row and then Sum 1

Top