Sub Test() Dim rng As Range Sheets("Data").Copy After:=Sheets(Sheets.Count) With ActiveSheet.Range("A6").CurrentRegion.Offset(1).Resize(, 12) With .Columns("L") .Formula = "=IF((LEN(J7)+LEN(K7))>0,1,IF(ISERROR(SEARCH(""cancel"",B7)),"""",1))" .Value = .Value End With .Sort key1:=.Columns("L"), header:=xlNo Set rng = Intersect(.Columns("L").SpecialCells(xlCellTypeBlanks).EntireRow, .Cells) With Sheets.Add(After:=Sheets(Sheets.Count)) rng.Parent.Range("A6:K6").Copy .Range("A1") rng.Cut .Range("A2") .Range("A1").CurrentRegion.EntireColumn.AutoFit End With .Columns("L").Clear End With End Sub
Bookmarks