Ahh... good. Sorry... I wasn't sure what you meant; perhaps I didn't read
carefully enough the first time.. sorry.

Anyway, here, this will do what you need.

'***********
Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd
Range("A3:N2000").Select

If Range("a3:N2000").SpecialCells(xlCellTypeVisible). Count <
Range("a3:N2000").Cells.Count Then

Selection.ClearContents

End If
'************

The special cells xlcelltypevisible bit will check if the autofilter has
hidden any rows, or not, and only run the clearcontents if the two counts
(total cells vs. visible cells) don't match.



"Ken" wrote:

What I wish:

1: Criteria found ... Clear visible cells in Range A3:N2000.
2: Criteria not found ...Move on without clearing anything.

As it is:

1: Criteria found ... Clears visible cells in Range A3:N2000.
2: Criteria not found ... Clears EVERYTHING in Range A3:N2000.

Thanks ... Kha




"mark" wrote:

your code is set to clear everything left visible in the range A3:N2000 after
the application of the AutoFilter.

what do you want it to do? You didn't actually say.

If you want it to apply the filter, but not delete anything, then just take
out the two line:

Selection.ClearContents





"Ken" wrote:

Excel2003 ... I know nothing of VB Code ... I record Macros only & then
perform a little creative cut/paste as needed ... That said ... I have the
following recorded Macro instruction:

Range("A2").Select
Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd
Range("A3:N2000").Select
Selection.ClearContents

Issue is ... ALL Data is cleared from the Range ("A3:N2000") when the
Criteria1:="<32*" is NOT found.

When Criteria1 is found ... no problem.
When Criteria1 is not found ... it sucks to be me.

My Thanks to those that support these boards ... Kha

If Range("a3:N2000").SpecialCells(xlCellTypeVisible). Count <
Range("a3:N2000").Cells.Count Then

Above piece of instruction lights up RED (Compile Error Expected Expression)
.... I added a space and an underscore after the < at the end of the 1st line
& RED & Error message went away ... However, when I ran the Macro & the
Criteria was NOT found ... The Macro returned error message = No Cells
found???

Thanks for sticking with me ... Kha


"mark" wrote:

Ahh... good. Sorry... I wasn't sure what you meant; perhaps I didn't read
carefully enough the first time.. sorry.

Anyway, here, this will do what you need.

'***********
Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd
Range("A3:N2000").Select

If Range("a3:N2000").SpecialCells(xlCellTypeVisible). Count <
Range("a3:N2000").Cells.Count Then

Selection.ClearContents

End If
'************

The special cells xlcelltypevisible bit will check if the autofilter has
hidden any rows, or not, and only run the clearcontents if the two counts
(total cells vs. visible cells) don't match.



"Ken" wrote:

What I wish:

1: Criteria found ... Clear visible cells in Range A3:N2000.
2: Criteria not found ...Move on without clearing anything.

As it is:

1: Criteria found ... Clears visible cells in Range A3:N2000.
2: Criteria not found ... Clears EVERYTHING in Range A3:N2000.

Thanks ... Kha




"mark" wrote:

your code is set to clear everything left visible in the range A3:N2000 after
the application of the AutoFilter.

what do you want it to do? You didn't actually say.

If you want it to apply the filter, but not delete anything, then just take
out the two line:

Selection.ClearContents





"Ken" wrote:

Excel2003 ... I know nothing of VB Code ... I record Macros only & then
perform a little creative cut/paste as needed ... That said ... I have the
following recorded Macro instruction:

Range("A2").Select
Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd
Range("A3:N2000").Select
Selection.ClearContents

Issue is ... ALL Data is cleared from the Range ("A3:N2000") when the
Criteria1:="<32*" is NOT found.

When Criteria1 is found ... no problem.
When Criteria1 is not found ... it sucks to be me.

My Thanks to those that support these boards ... Kha

One more thing ... I have "multiple" checks to make & I wish to "clear
contents" each time Criteria found ... & I wish to MOVE ON each time Criteria
is NOT found.

Thanks ... Kha

"mark" wrote:

Ahh... good. Sorry... I wasn't sure what you meant; perhaps I didn't read
carefully enough the first time.. sorry.

Anyway, here, this will do what you need.

'***********
Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd
Range("A3:N2000").Select

If Range("a3:N2000").SpecialCells(xlCellTypeVisible). Count <
Range("a3:N2000").Cells.Count Then

Selection.ClearContents

End If
'************

The special cells xlcelltypevisible bit will check if the autofilter has
hidden any rows, or not, and only run the clearcontents if the two counts
(total cells vs. visible cells) don't match.



"Ken" wrote:

What I wish:

1: Criteria found ... Clear visible cells in Range A3:N2000.
2: Criteria not found ...Move on without clearing anything.

As it is:

1: Criteria found ... Clears visible cells in Range A3:N2000.
2: Criteria not found ... Clears EVERYTHING in Range A3:N2000.

Thanks ... Kha




"mark" wrote:

your code is set to clear everything left visible in the range A3:N2000 after
the application of the AutoFilter.

what do you want it to do? You didn't actually say.

If you want it to apply the filter, but not delete anything, then just take
out the two line:

Selection.ClearContents





"Ken" wrote:

Excel2003 ... I know nothing of VB Code ... I record Macros only & then
perform a little creative cut/paste as needed ... That said ... I have the
following recorded Macro instruction:

Range("A2").Select
Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd
Range("A3:N2000").Select
Selection.ClearContents

Issue is ... ALL Data is cleared from the Range ("A3:N2000") when the
Criteria1:="<32*" is NOT found.

When Criteria1 is found ... no problem.
When Criteria1 is not found ... it sucks to be me.

My Thanks to those that support these boards ... Kha

yeah, the post was unclear because of how this thing breaks lines, but the
whole IF condition, through the the THEN, needed to be on one line, or have a
continuation character put in, as you did.

But I'm not getting the other error that you got in my test.

Here's what I have, for a little sample range of A1:A10

A1: Type, A2 through A10 have 'a' in it, so that the condition will not be
found.

Then, the code is:

Sub test()
Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd
Range("A2:a10").Select
If Range("a2:a10").SpecialCells(xlCellTypeVisible).Co unt <
Range("a2:a10").Cells.Count Then
Selection.ClearContents
End If
End Sub

where the If condition has put itself on two lines, in here, again, but is
on one line in the VBA code module.

It runs fine.

Can you post exactly where your version is getting an error? What code
line, I mean.




"Ken" wrote:

If Range("a3:N2000").SpecialCells(xlCellTypeVisible). Count <
Range("a3:N2000").Cells.Count Then

Above piece of instruction lights up RED (Compile Error Expected Expression)
... I added a space and an underscore after the < at the end of the 1st line
& RED & Error message went away ... However, when I ran the Macro & the
Criteria was NOT found ... The Macro returned error message = No Cells
found???

Thanks for sticking with me ... Kha


"mark" wrote:

Ahh... good. Sorry... I wasn't sure what you meant; perhaps I didn't read
carefully enough the first time.. sorry.

Anyway, here, this will do what you need.

'***********
Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd
Range("A3:N2000").Select

If Range("a3:N2000").SpecialCells(xlCellTypeVisible). Count <
Range("a3:N2000").Cells.Count Then

Selection.ClearContents

End If
'************

The special cells xlcelltypevisible bit will check if the autofilter has
hidden any rows, or not, and only run the clearcontents if the two counts
(total cells vs. visible cells) don't match.



"Ken" wrote:

What I wish:

1: Criteria found ... Clear visible cells in Range A3:N2000.
2: Criteria not found ...Move on without clearing anything.

As it is:

1: Criteria found ... Clears visible cells in Range A3:N2000.
2: Criteria not found ... Clears EVERYTHING in Range A3:N2000.

Thanks ... Kha




"mark" wrote:

your code is set to clear everything left visible in the range A3:N2000 after
the application of the AutoFilter.

what do you want it to do? You didn't actually say.

If you want it to apply the filter, but not delete anything, then just take
out the two line:

Selection.ClearContents





"Ken" wrote:

Excel2003 ... I know nothing of VB Code ... I record Macros only & then
perform a little creative cut/paste as needed ... That said ... I have the
following recorded Macro instruction:

Range("A2").Select
Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd
Range("A3:N2000").Select
Selection.ClearContents

Issue is ... ALL Data is cleared from the Range ("A3:N2000") when the
Criteria1:="<32*" is NOT found.

When Criteria1 is found ... no problem.
When Criteria1 is not found ... it sucks to be me.

My Thanks to those that support these boards ... Kha

how to do this will depend upon how your data is arranged... on one sheet?
on mutiple sheets?

you can turn the AutoFilter for a given sheet off again by adding

Selection.Autofilter

to the end of your code... that flips whether it's on... if it's on, it
turns it off, if it's off, it turns it on.

If you need to have your code check if an AutoFilter mode is on, and turn it
on or off, you can do it like this:

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

That would turn the AutoFilter off, if it were on. Change the True to
False, if you need it to turn it on, if it's off.

You didn't mention how your data is arranged, one sheet, or multiples... but
if it's a series of sheets that you need to perform this same check on, it
would go something like this:

Dim i as integer

For i = 1 to ActiveWorkbook.Shets.Count Step 1
Sheets(i).Activate

'The rest of your code that we've been discussing here


Next i


That will loop through every sheet in the workbook, performing the same check.

If your data is arranged in another manner, you'd need to edit it
appropriately.

Macro is checking 1 WorkSheet only, but is checking 4 different Criteria.

When Criteria is NOT found ... Excel debugger is stopping on & highlighting
the 2 lines of Code pasted in from your post (starting with "If Range") ...
Also, a little pop-up menu appears stating "Cells not found" & Range A3:N2000
is not visible.

Here is my recorded Macro in its entirety with your Macro instructions
pasted in.

====
' Test_Scrub Macro
' Macro recorded 3/18/2008
'
'
ActiveSheet.Unprotect

Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd

Range("A3:N2000").Select

If Range("A3:N2000").SpecialCells(xlCellTypeVisible). Count < _
Range("A3:N2000").Cells.Count Then

Selection.ClearContents

End If

Selection.AutoFilter Field:=1

Selection.AutoFilter Field:=2, Criteria1:="=*052", Operator:=xlAnd

Range("A3:N2000").Select

If Range("A3:N2000").SpecialCells(xlCellTypeVisible). Count < _
Range("A3:N2000").Cells.Count Then

Selection.ClearContents

End If

Selection.AutoFilter Field:=2

Selection.AutoFilter Field:=3, Criteria1:="=112*", Operator:=xlAnd

Range("A3:N2000").Select

If Range("A3:N2000").SpecialCells(xlCellTypeVisible). Count < _
Range("A3:N2000").Cells.Count Then

Selection.ClearContents

End If

Selection.AutoFilter Field:=3

Selection.AutoFilter Field:=3, Criteria1:="=169*", Operator:=xlAnd

Range("A3:N2000").Select

If Range("A3:N2000").SpecialCells(xlCellTypeVisible). Count < _
Range("A3:N2000").Cells.Count Then

Selection.ClearContents

Selection.AutoFilter Field:=3

End If

Range("A2:N2000").Select
Range("N2").Activate
Selection.Sort Key1:=Range("E3"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("F3").Select

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

Range("A3").Select

End Sub

====

I will be back at this tomorrow ... Thank you in advance for your patience &
guidance ... Kha



"mark" wrote:

how to do this will depend upon how your data is arranged... on one sheet?
on mutiple sheets?

you can turn the AutoFilter for a given sheet off again by adding

Selection.Autofilter

to the end of your code... that flips whether it's on... if it's on, it
turns it off, if it's off, it turns it on.

If you need to have your code check if an AutoFilter mode is on, and turn it
on or off, you can do it like this:

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

That would turn the AutoFilter off, if it were on. Change the True to
False, if you need it to turn it on, if it's off.

You didn't mention how your data is arranged, one sheet, or multiples... but
if it's a series of sheets that you need to perform this same check on, it
would go something like this:

Dim i as integer

For i = 1 to ActiveWorkbook.Shets.Count Step 1
Sheets(i).Activate

'The rest of your code that we've been discussing here


Next i


That will loop through every sheet in the workbook, performing the same check.

If your data is arranged in another manner, you'd need to edit it
appropriately.

I didn't try to make a program to check this but did you look at the post by
myself and Ron DeBruin?
If you like send a workbook (with snippets of these messages so I'll know
what you want) to my address below and I will take a look.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ken" wrote in message
...
Macro is checking 1 WorkSheet only, but is checking 4 different Criteria.

When Criteria is NOT found ... Excel debugger is stopping on &
highlighting
the 2 lines of Code pasted in from your post (starting with "If Range")
...
Also, a little pop-up menu appears stating "Cells not found" & Range
A3:N2000
is not visible.

Here is my recorded Macro in its entirety with your Macro instructions
pasted in.

====
' Test_Scrub Macro
' Macro recorded 3/18/2008
'
'
ActiveSheet.Unprotect

Selection.AutoFilter Field:=1, Criteria1:="<32*", Operator:=xlAnd

Range("A3:N2000").Select

If Range("A3:N2000").SpecialCells(xlCellTypeVisible). Count < _
Range("A3:N2000").Cells.Count Then

Selection.ClearContents

End If

Selection.AutoFilter Field:=1

Selection.AutoFilter Field:=2, Criteria1:="=*052", Operator:=xlAnd

Range("A3:N2000").Select

If Range("A3:N2000").SpecialCells(xlCellTypeVisible). Count < _
Range("A3:N2000").Cells.Count Then

Selection.ClearContents

End If

Selection.AutoFilter Field:=2

Selection.AutoFilter Field:=3, Criteria1:="=112*", Operator:=xlAnd

Range("A3:N2000").Select

If Range("A3:N2000").SpecialCells(xlCellTypeVisible). Count < _
Range("A3:N2000").Cells.Count Then

Selection.ClearContents

End If

Selection.AutoFilter Field:=3

Selection.AutoFilter Field:=3, Criteria1:="=169*", Operator:=xlAnd

Range("A3:N2000").Select

If Range("A3:N2000").SpecialCells(xlCellTypeVisible). Count < _
Range("A3:N2000").Cells.Count Then

Selection.ClearContents

Selection.AutoFilter Field:=3

End If

Range("A2:N2000").Select
Range("N2").Activate
Selection.Sort Key1:=Range("E3"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("F3").Select

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

Range("A3").Select

End Sub

====

I will be back at this tomorrow ... Thank you in advance for your patience
&
guidance ... Kha



"mark" wrote:

how to do this will depend upon how your data is arranged... on one
sheet?
on mutiple sheets?

you can turn the AutoFilter for a given sheet off again by adding

Selection.Autofilter

to the end of your code... that flips whether it's on... if it's on, it
turns it off, if it's off, it turns it on.

If you need to have your code check if an AutoFilter mode is on, and turn
it
on or off, you can do it like this:

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

That would turn the AutoFilter off, if it were on. Change the True to
False, if you need it to turn it on, if it's off.

You didn't mention how your data is arranged, one sheet, or multiples...
but
if it's a series of sheets that you need to perform this same check on,
it
would go something like this:

Dim i as integer

For i = 1 to ActiveWorkbook.Shets.Count Step 1
Sheets(i).Activate

'The rest of your code that we've been discussing here


Next i


That will loop through every sheet in the workbook, performing the same
check.

If your data is arranged in another manner, you'd need to edit it
appropriately.

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

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

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

Mark ... If you are still listening?

So ... what would this Instruction look like? Remember, I do not have a
clue about VB Code (nadda, zilch, zero) ...

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

Thanks ... Kha

"mark" wrote:

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