On sheet1, In column D, If any of the cells contains "Record Only" I need it to delete the entire row along with next 15 decending rows to be delete.
Thanks.
On sheet1, In column D, If any of the cells contains "Record Only" I need it to delete the entire row along with next 15 decending rows to be delete.
Thanks.
Attach an example workbook
Hi,
Sample Workbook attached.
Thanks.
The fastest way ts to utilise AutoFilter
Sub deleteFilteredData()
Dim rDelete As Range
Dim lCalc As Long
Dim sDelete As String
With Application
lCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
On Error GoTo exit_proc
sDelete = "Record Only"
'Sheet with the data, change the name
With Sheet1
.AutoFilterMode = False
'Apply the filter, this range of data starts in A1
.Cells(1, 1).CurrentRegion.AutoFilter Field:=4, Criteria1:=sDelete
With .AutoFilter.Range
On Error Resume Next
Set rDelete = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
' On Error GoTo 0
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End With
'Remove the AutoFilter
.AutoFilterMode = False
End With
exit_proc:
.ScreenUpdating = True
.Calculation = lCalc
End With
End Sub
Display More
CurrentRegion stops at an empty row, so the "Record Only" would not be included. Try
Don’t have an account yet? Register yourself now and be a part of our community!