Insert a blanc row at a data change in column - office 365 dutch

[charlottedv]

Hi!
I'm trying to add a blanc row everytime there's a change in data in one of my columns.
I've read already some other threads with similar questions like: Thread 1 and Thread 2 but none of the codes mentioned here seem to work for me.
I get the error: "types do not match". Could it have to do with the fact that my office language is set in Dutch? (belgian user here )

In this image you can see the basic set up of my data sheet.
I would need a blanc row everytime the column "Variantcode" changes.
Any solution or help to automate this proces is welcome, VBA or basic excel actions.

 

[charlottedv]

Try this:

Sub MyInsertBlankRows()

    Dim lr As Long
    Dim r As Long
   
    On Error GoTo err_chk
   
    Application.ScreenUpdating = False
   
'   Find last row in column Z with data
    lr = Cells(Rows.Count, "Z").End(xlUp).Row
   
'   Loop through all rows starting from bottom up to row 3
    For r = lr To 3 Step -1
'       Check to see if value is different from row above
        If Cells(r, "Z") <> Cells(r - 1, "Z") Then
'           Insert blank rows
            Rows(r).Insert
'           Populate values in new row
            Cells(r, "E") = Cells(r + 1, "E")
            Cells(r, "G") = "matrix"
            Cells(r, "H") = Cells(r + 1, "H")
            Cells(r, "K") = Cells(r + 1, "K")
            Cells(r, "L") = Cells(r + 1, "L")
            Cells(r, "M") = Cells(r + 1, "M")
            Cells(r, "Z") = Cells(r + 1, "Z")
            Cells(r, "Y") = Cells(r, "Z")
        End If
    Next r
   
    Application.ScreenUpdating = True
   
    Exit Sub
   
err_chk:
    If Err.Number = 13 Then
        MsgBox "Error is near line " & r
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
   
    Application.ScreenUpdating = True
   
End Sub

This works perfectly, thank you!

 

[Joe4]

You are welcome.