When run the above macro on Book1.xlsx, data in col AB and CD both change to weekly one. I want data in col CD intact whenSub w() Dim i As Long, rng As Range Columns(1).Insert With Range("b2", Range("b" & Rows.Count).End(xlUp)).Offset(, -1) .Formula = "=if(and(weekday(b2,2)<weekday(b3,2),b3<>""""),1,"""")" .Value = .Value On Error Resume Next .SpecialCells(2, 1).EntireRow.Delete On Error GoTo 0 End With Columns(1).Delete End Sub
running the macro.