VBA Macro for adding a blank row after comparing Data

[tlaltmey]

Hi,

I'm looking at creating a basic macro that compares data from Column D. The goal is to add a blank row when the data changes, but only for the first 7 digits.

For example:

1.) 0044982-001
2.) 0044982-002
3.) 0044982-003
4.) 0045000-001
5.) 0045000-002
6.) 0012345-001

Then becomes
1.) 0044982-001
2.) 0044982-002
3.) 0044982-003
4.) 0045000-001
5.) 0045000-002
6.) 0012345-001

Currently I have the following:

Dim lRow As Long
For lRow = Cells(Cells.Rows.count, "D").End(xlUp).Row To 2 Step -1
    If Cells(lRow, "D") <> Cells(lRow - 1, "D") Then
        Rows(lRow).EntireRow.Insert
        
        ' Adds a gray filled bar in empty rows
        Rows(lRow).Interior.ColorIndex = 48
    End If
Next lRow

This code adds a space between all data, as the last digit continues to change, which is not what I want.

 

[Joe4]

Welcome to the Board!

If you only want to look at the first 7 characters, try changing this line:

    If Cells(lRow, "D") <> Cells(lRow - 1, "D") Then

to

    If Left(Cells(lRow, "D"),7) <> Left(Cells(lRow - 1, "D"),7) Then

 

[Fluff]

Hi & welcome to MrExcel.
How about

Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row To 2 Step -1
    If Left(Cells(lRow, "D"), 7) <> Left(Cells(lRow - 1, "D"), 7) Then
        Rows(lRow).EntireRow.Insert
        
        ' Adds a gray filled bar in empty rows
        Rows(lRow).Interior.ColorIndex = 48
    End If
Next lRow

 

[tlaltmey]

Welcome to the Board!

If you only want to look at the first 7 characters, try changing this line:

    If Cells(lRow, "D") <> Cells(lRow - 1, "D") Then

to

    If Left(Cells(lRow, "D"),7) <> Left(Cells(lRow - 1, "D"),7) Then

Thanks Joe,

This is exactly what I was looking for.

 

[Joe4]

You are welcome.
Glad we were able to help!