Is there a way to create a Macro to insert a blank row based on the change in cell value in multiple columns?

[kokoanutt]

I have already been able to create a Macro to insert a blank row based on the change in value for column C, but I also need it to look at column G. In this example, I also need a blank row in between the "ORION" and the "OCEAN" data:

Please help. Thank You!

 

[eiloken]

try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cll As Range, rng As Range, irng As Range
    Dim i As Long
    i = Me.Range("G" & Rows.Count).End(xlUp).Row
    If i = 0 Then Exit Sub
    Set rng = Me.Range("G1:G" & i)
    If Not Intersect(Target, rng) Is Nothing Then
        For Each cll In rng
            If cll.Value Like "*ORION*" And cll.Offset(1).Value Like "*OCEAN*" Then
                If irng Is Nothing Then Set irng = cll.Offset(1) Else Set irng = Union(irng, cll.Offset(1))
            End If
        Next cll
        If Not irng Is Nothing Then irng.EntireRow.Insert shift:=xlDown
    End If
End Sub

if you already have the code to automatically run when value in column C change then you need to merge it with this code and make sure it work well with another

 

[kokoanutt]

Thank you @eiloken
I completely forgot to post the code that I am currently using to insert a blank row in between different values in column C:

Sub BLANKS_SUMMARY2()

Dim ws As Worksheet
Dim lr As Long
Dim i As Long

Set ws = Worksheets("Logged Units Report") 'the sheet with the data
lr = ws.Range("F" & Rows.Count).End(xlUp).Row 'last row with data in Column F
For i = lr - 1 To 2 Step -1
    If ws.Range("F" & i).Value <> ws.Range("F" & i + 1).Value Then ws.Range("F" & i + 1).EntireRow.Insert
Next i

End Sub

 

[kokoanutt]

Thank you @eiloken
I completely forgot to post the code that I am currently using to insert a blank row in between different values in column C:

Sub BLANKS_SUMMARY2()

Dim ws As Worksheet
Dim lr As Long
Dim i As Long

Set ws = Worksheets("Logged Units Report") 'the sheet with the data
lr = ws.Range("C" & Rows.Count).End(xlUp).Row 'last row with data in Column C
For i = lr - 1 To 2 Step -1
    If ws.Range("C" & i).Value <> ws.Range("C" & i + 1).Value Then ws.Range("C" & i + 1).EntireRow.Insert
Next i

End Sub

 

[kokoanutt]

I am new to all of this. I really just need to add a blank row each time there is a difference in cell value in columns C and G.

 

[Alex Blakenburg]

Your code refers to column F but your image and description refer to column C.
If you run both sets of logic at the same time the below should work for you.
If you want to run them separately we need additional logic to cater for blank rows.

Sub BLANKS_SUMMARY2()

    Dim ws As Worksheet
    Dim lr As Long
    Dim i As Long
   
    Set ws = Worksheets("Logged Units Report")              'the sheet with the data
    lr = ws.Range("C" & Rows.Count).End(xlUp).Row           'last row with data in Column F
    For i = lr - 1 To 2 Step -1
        If ws.Range("C" & i).Value <> ws.Range("C" & i + 1).Value _
            Or ws.Range("G" & i).Value <> ws.Range("G" & i + 1).Value Then
                ws.Range("C" & i + 1).EntireRow.Insert
        End If
    Next i

End Sub

 

[kokoanutt]

That worked!!! Thank you so much @Alex Blakenburg

I realized that I posted the wrong code so then I quickly posted the correct code as I was unsure if I was able to delete a post.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.

 

[kokoanutt]

Your code refers to column F but your image and description refer to column C.
If you run both sets of logic at the same time the below should work for you.
If you want to run them separately we need additional logic to cater for blank rows.

Sub BLANKS_SUMMARY2()

    Dim ws As Worksheet
    Dim lr As Long
    Dim i As Long
  
    Set ws = Worksheets("Logged Units Report")              'the sheet with the data
    lr = ws.Range("C" & Rows.Count).End(xlUp).Row           'last row with data in Column F
    For i = lr - 1 To 2 Step -1
        If ws.Range("C" & i).Value <> ws.Range("C" & i + 1).Value _
            Or ws.Range("G" & i).Value <> ws.Range("G" & i + 1).Value Then
                ws.Range("C" & i + 1).EntireRow.Insert
        End If
    Next i

End Sub

@Alex Blakenburg, is there a way to also add in two blank rows when the date changes in Column A?

 

[kokoanutt]

@Alex Blakenburg, is there a way to also add in two blank rows when the date changes in Column A?

@Alex Blakenburg, or is there a way to insert a blank row and then shade it when the date changes in Column A? This might be more useful.