I am receiving this error when I run the macro. (compilation error, expected
end sub) It is having an error where shown below. I am very sorry, but your
first reply had in it what I was looking for. Just now I am having this error
and don't know what to do about it. I have tried typing in end sub in a
couple locations, but not luck with it yet.

Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String
(compilation error, expected end sub)
Sub ChangeFilters()

Set w = Worksheets("Screener")
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For f = 1 To .Count
With .Item(f)
--



"Joel" wrote:

This is the VBA help (excel 2003) under the topic Autofilter Object

Using the AutoFilter Object
Use the AutoFilter property to return the AutoFilter object. Use the Filters
method to return a collection of individual column filters. Use the Range
method 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.

Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String

Sub ChangeFilters()

Set w = Worksheets("Crew")
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f, 1) = .Criteria1
If .Operator Then
filterArray(f, 2) = .Operator
filterArray(f, 3) = .Criteria2
End If
End If
End With
Next
End With
End With

w.AutoFilterMode = False
w.Range("A1").AutoFilter field:=1, Criteria1:="S"

End Sub
To create an AutoFilter object for a worksheet, you must turn autofiltering
on for a range on the worksheet either manually or using the AutoFilter
method of the Range object. The following example uses the values stored in
module-level variables in the previous example to restore the original
autofiltering to the Crew worksheet.

Sub RestoreFilters()
Set w = Worksheets("Crew")
w.AutoFilterMode = False
For col = 1 To UBound(filterArray(), 1)
If Not IsEmpty(filterArray(col, 1)) Then
If filterArray(col, 2) Then
w.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1), _
Operator:=filterArray(col, 2), _
Criteria2:=filterArray(col, 3)
Else
w.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1)
End If
End If
Next
End Sub


"Doug" wrote:

How can I have excel save the current filter and sort settings prior to
updating my sheet and then reapply them at the end of the update? I need a
formula to insert in my VBA to perform this function. Does someone know how?
--