1819
01-19-2017, 03:07 PM
This code (from the VBA Recorder) works well to find rows containing certain words and delete the whole row. (The rows in this instance only actually have column A filled, but I would the flexibility to do it across more columns).
However, I'd like to improve this by:
1) asking how the search terms could be combined into an array, so only one block of code would be necessary
2) asking whether there is a quicker method of doing the same thing.
Many thanks.
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*award*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*thanks*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*job*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*join us*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*joining us*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
However, I'd like to improve this by:
1) asking how the search terms could be combined into an array, so only one block of code would be necessary
2) asking whether there is a quicker method of doing the same thing.
Many thanks.
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*award*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*thanks*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*job*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*join us*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "=*joining us*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With