Insert multiple rown at each change in data

[lewis_green]

Hi guys. I'm looking for a way to insert 5 blank rows between each change in data in Column B of my spreadsheet.

Currently I am using the following formula which inserts one row, but I have found nothing anywhere for a code that enters more than one row.


Sub MacroChangeindata()
'
' MacroChangeindata Macro
'
'Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then Rows(lRow).EntireRow.Insert

Next lRow
End Sub

Any help would be appreciated!

Thanks
Lewis

 

[VoG]

Hi. Try this

Sub MacroChangeindata()
'
' MacroChangeindata Macro
'
'Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then Rows(lRow).Resize(5).Insert

Next lRow
End Sub

 

[lewis_green]

Thanks mate!!!

It worked a treat