insert blank row at every change in a column VBA

[sjk1193]

Hi there,

I have the following code that is supposed to insert a blank row at every change change in column 5, but for some reason it is not working. Its a relatively simple code so I dont know what the problem can be. It seems to be working properly on some worksheets but not others, but not sure why

Sub insertblankrow()


Dim change As Range
Set change = ActiveSheet.UsedRange
    
    For i = change.Rows.Count To 3 Step -1
        If Cells(i, 5).Value <> Cells(i - 1, 5).Value Then
            ActiveSheet.Rows(i).Insert Shift:=xlDown
        End If
    Next


End Sub

 

[Scott T]

Try

Sub insertrow()

Dim lr As Long
lr = Cells(Rows.Count, 5).End(xlUp).Row


For i = lr To 3 Step -1
    If Cells(i, 5) <> Cells(i - 1, 5) Then Rows(i).EntireRow.Insert
Next i
End Sub

 

[sjk1193]

thanks that worked

 

[Scott T]

You're welcome