"Steve" wrote in message
...
Hi Clif & Garry,

Clif - some sample code would be greatly appreciated!



From the built-in help for Autofilter Object:
Use the AutoFilter property to return the AutoFilter object. Use the
Filters property to return a collection of individual column filters.
Use the Range property to return the Range object that represents the
entire filtered range. The following example stores the address and
filtering criteria for the current filtering and then applies new
filters.

I used that example code along with the macro recorder as my starting
point when beginning to work with autofilters.


Here's a code snippet from a macro I wrote when I needed to make certain
that "empty" cells were in fact empty:

(the worksheet already had an active autofilter range defined)

For x = 1 To 67 '67 columns
Selection.AutoFilter Field:=x, Criteria1:="=" 'Filter on Blank
Cells(5, x).EntireColumn.Select 'Select all but hidden
cells
Selection.ClearContents 'Delete contents
ActiveSheet.ShowAllData 'Show All Data
Next x
-------------

The next code snippet sets up an autofilter on a data sheet that drives
a chart sheet; based on user selection from a form. varMore() gives me
the flexibility to optionally add additional criteria for the
autofilter:

Sub MixChart(Mix As String, ParamArray varMore())
' ParamArray pairs = sort field, criteria
wbSaved = ActiveWorkbook.Saved 'unsaved changes?
'// I don't want to treat a change to the visible autofilter as a
change to the workbook '// but if there are other user changes I
want Excel to remember that!
With Worksheets("Data")
If Not .Range("D7").Value = Mix Or Mix = "Mix 3BF" Then
'// if the autofilter is already set correctly don't do it again
If .FilterMode Then .ShowAllData
'// When I wrote this I didn't use With / End With .... if I wrote
this
'// today I'd wrap this inside "With .Range("cylData").AutoFilter"
.Range("cylData").AutoFilter Field:=4, Criteria1:="=*" & Mix & "*", _
Operator:=xlAnd, Criteria2:="<*-*"
.Range("cylData").AutoFilter Field:=24, Criteria1:="<"
Select Case UBound(varMore)
Case 1
.Range("cylData").AutoFilter Field:=varMore(0), _
Criteria1:=varMore(1)
End Select
'// the End With would go here
.Range("D7").Value = Mix
End If
End With
If Not ActiveSheet.Name = "Chart1" Then Sheets("Chart1").Select
ActiveWorkbook.Saved = wbSaved 'restore previous state
End Sub
-----------------

To disable the autofilter conditions in a single column:

Range("cylData").AutoFilter Field:=4 'Cancel Location Filter
(the ShowAllData method cancels the filters in all columns)

----------------

I have other code that manipulates autofilters; just now I'm not
remembering just where I record a current definition for later use; but
this code along with the sample in the help should get you going. The
steps to copy the hidden data should be:



1. Record the current autofilter criteria
2. ShowAllData to reveal the hidded rows
3. Do your copy
4. Re-apply the autofilter criteria

All this should be done with screen updating turned off ... unless you
want the user to see all the scree updating happen. The macro is *much*
faster without the screen updating.


Post back if you need more specific help.


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)