There are two lines of hardcode that should be fixed. The first is where
I'm assuming that a header row exists that you don't want touched (Assumes
header). You might what to make the row number higher or lower depending on
your situation. Good code will test the worksheet to determine the last row
of the header.
The second lines is the Selection.Autofilter. The Criteria is assumed to be
"x" and the column is assumed to be the second column. To force it to
column P the 2 needs to be changed to 16. Again, this is hardcode that
should be eliminated. At least add them as constants in the declarations to
document their use.
Essentially, the code may work if you just change 2 to 16.
BTW, this is better than looping as long as a single column can be used to
determine the action.
One other thing. If you have a lot of formulas in your worksheet, you might
find that Filters take forever to recalculate. IF so, use this code in the
same places that ScreenUpdating was used.
Dim lngInitalCalculationMode As Long
lngInitalCalculationMode = Application.Calculation
If lngInitalCalculationMode = xlCalculationManual Then
Else
Application.Calculation = xlManual
End If
Be sure to set the CalcuationMode back to the user settings in the
ExitRoutine with this code
If lngInitalCalculationMode = xlCalculationManual Then
Application.Calculation = xlManual
Else
Application.Calculation = xlAutomatic
End If
Range("A2").Select '<===== assumes header
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter Field:=2, Criteria1:="x" '<=== get rid of
hard code
--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.
"Lift Off" wrote in
message ...
No formulas in 'P', just 'x' or 'y' in every row. Calc and updating are
off.
Tom: Tried your suggestion and it wouldn't compile. Problem with the
End If statements. I tried inserting one he
If LCase(Arng(j).Value) = "x" Then _
If rng Is Nothing Then _
Set rng = Arng(j) Else _
Set rng = Union(rng, Arng(j))
End If
End If
Next j
but I was wasn't correct.
AEN: Novice? Hardly!
Tried the second set of code. Didn't work. If I'm trying to filter
"P", where is that identified? The code sets up the filter by stops
there. No deletion of rows. Data is in every row of P.
Thanks for the quick response.
--
Lift Off
------------------------------------------------------------------------
Lift Off's Profile:
http://www.excelforum.com/member.php...fo&userid=8249
View this thread: http://www.excelforum.com/showthread...hreadid=507779