You can try it like this:
Sub test1()
With ActiveSheet
If Application.CountBlank(Intersect(.Columns("D"), .UsedRange)) > 0 Then
Intersect(.Columns("D"), .UsedRange).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
If Application.CountBlank(Intersect(.Columns("B"), .UsedRange)) > 0 Then
Intersect(.Columns("B"), .UsedRange).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
With Intersect(.Columns("B"), .UsedRange)
.Value = .Value
End With
End If
With .Range("A1").CurrentRegion
.AutoFilter 1, "S/N"
Application.DisplayAlerts = False
.Rows("2:" & .Rows.Count).Delete
Application.DisplayAlerts = True
.AutoFilter
End With
End With
End Sub
Bookmarks