Hi all, I really hope you can help me. I have an issue writing my code. I have a range, where first I am inserting blank cells whenever the value/text in column N changes - this I have solved. Now I want to copy the header (row 1) into all the newly created blank rows. However this should only be done in a dynamic range (maybe using a formula to find last used row). Really hoping you can help.
Sub InsertRowsAtValueChangeColumn()
Dim X As Long, LastRow As Long
Const DataCol As String = "N"
Const StartRow = 2
LastRow = Cells(rows.Count, DataCol).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To StartRow + 1 Step -1
If Cells(X, DataCol).Value <> Cells(X - 1, DataCol) Then rows(X).insert
Next
'Application.ScreenUpdating = True
'rows(1).EntireRow.copy
'Selection.SpecialCells(xlCellTypeBlanks.PasteSpecial
'Range("A1:X1").Select
'Selection.copy
'BlankCell.Paste
'Destination = Selection.SpecialCells(xlCellTypeBlanks)
End Sub
Sub InsertRowsAtValueChangeColumn()
Dim X As Long, LastRow As Long
Const DataCol As String = "N"
Const StartRow = 2
LastRow = Cells(rows.Count, DataCol).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To StartRow + 1 Step -1
If Cells(X, DataCol).Value <> Cells(X - 1, DataCol) Then rows(X).insert
Next
'Application.ScreenUpdating = True
'rows(1).EntireRow.copy
'Selection.SpecialCells(xlCellTypeBlanks.PasteSpecial
'Range("A1:X1").Select
'Selection.copy
'BlankCell.Paste
'Destination = Selection.SpecialCells(xlCellTypeBlanks)
End Sub