A little Macro Help Please ...
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.
|