you could just do a resort then instead of the .entirerow.delete

"Ken" wrote:

Mark ... (Good morning)

Thank you for the effort you have put into responding to this post ...
However, I can not Delete Entire Rows as this is a Template that I must paste
data into every day & then Scrub (clear contents of all unwanted data) ...
Also, there are Cols (some with Formulas) to the Right of my indicated Range
(A3:N2000).

Above said ... I thought I could simplify the scrubbing process further ...
& to some extent I have ... but I am now beginning to think the effort here
may not be worth the added benefit.

Once again, I recognize the short-coming is on my part ... And I thank you
for your efforts ... Kha


"mark" wrote:

I've worked up the sample as posted below...

Based upon what you provided, my understanding of what you want to do is this:

1) Eliminate data in rows where column 1 starts with 32
2) Eliminate data in rows where column 2 ends in 052
3) Eliminate data in rows where column 3 starts with 112
4) Eliminate data in rows where column 3 starts with 169

As it was eliminating data, I was running into trouble with the autofilter
not recognizing the data range any more, so I made one assumption (which may
be incorrect). I made the assumption that you have no data to the right of
your last column in the data range. With that assumption, I just had it
delete the row, instead of clear the cell contents.

The code below has a 2 dimension array which contains the field to apply the
criterion to, and the criterion to apply.

Then, the loop loops from the lower bound of the array to the upper bound,
applying the criteria, and deleting the visible rows.

Then, at the end, it sorts the remaining data in ascending order by column
E, places the cursor in the cell F3, and turns the protection back on.

If I have misunderstood any of what you requested, you may need to edit this
some. But, it should give a good start.

I can tell from what you already wrote that you know recorded code is
inflexible. Basically, it's a great way to learn how Visual Basic for
Applications (VBA) might do somethig in Excel, but it's usually too
inflexible to actually leave the code that way.

With the sample below, if you needed to change your data range, you could
just redefine the constant at the top. If you needed to delete a criterion,
add another, or change one, you would just redefine the array elements at the
top, and the rest of the code would not need to be changed.

Give it a look, see if it helps. You will again need to be careful with the
line wrapping that occurs here... may need to put the coditions and things
back on one line:

Sub sbScrub()

'dimension variables

Dim arCriterion(3, 1) As String
Dim i As Integer


'define constant, dimension variables

Const cnDataRange = "A3:N2000"
arCriterion(0, 0) = 1
arCriterion(0, 1) = "<32*"
arCriterion(1, 0) = 2
arCriterion(1, 1) = "=*052"
arCriterion(2, 0) = 3
arCriterion(2, 1) = "=112*"
arCriterion(3, 0) = 3
arCriterion(3, 1) = "=169*"


'unprotect the sheet, turn off autofilter, position cursor at cell A2

ActiveSheet.Unprotect
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
End If


'loop through array of fields/conditions

For i = LBound(arCriterion) To UBound(arCriterion) Step 1

Range("a2").Select
Selection.AutoFilter Field:=arCriterion(i, 0),
Criteria1:=arCriterion(i, 1), Operator:=xlAnd
Range(cnDataRange).Select

If Range(cnDataRange).SpecialCells(xlCellTypeVisible) .Count <
Range(cnDataRange).Cells.Count Then


Range(cnDataRange).SpecialCells(xlCellTypeVisible) .EntireRow.Delete

End If
Selection.AutoFilter

Next i


'sort the resultant dataset by the value in column E

Range(cnDataRange).Sort Key1:=Range("E3"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


'position cursor at cell F3

Range("F3").Select


'reset sheet protection

ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True

End Sub