Filter & sort
 

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?
--

Filter & sort
 

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?
--

Filter & sort
 

I am a little bit confused. What formula do I need to insert prior to the
update to remember the settings, and then what formula do I need to insert
after the update to reapply?
--
Thanks


"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?
--

Filter & sort
 

the example microsoft provided isn't very clear. The two subs really should
of been one sub.

The first sub puts the filter settings in an array

filterArray(1 To .Count, 1 To 3)

the second sub assumes you hav eput the settings into the same array and you
need to restore the values.

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


Are pulblic variables that are shared by both subs your code would look like
this

sub Main()

ChangeFilters
'enter you r code here
RestoreFilters
end sub



"Doug" wrote:

I am a little bit confused. What formula do I need to insert prior to the
update to remember the settings, and then what formula do I need to insert
after the update to reapply?
--
Thanks


"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?
--

Filter & sort
 

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?
--

Filter & sort
 

This is the orginal code that compiles without any errors. It is straight
from the microsoft help page except I added the main routine to show you how
to use the functions.

You code was giving errors because you didn't have matched blocks. Some
block types are as following
''''''''''''''''''''''''''''''''
if

end if
''''''''''''''''''''''''''''''''
with sheets("Sheet1")


end with
''''''''''''''''''''''''''''''''''''''''''''''
for i = 1 to 10

next i
''''''''''''''''''''''''''''''''''''''''''''''
do while i < 10

loop
''''''''''''''''''''''''''''''''''''''''''''''''''

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

Sub Main()

ChangeFilters
'enter you r code here
RestoreFilters
End Sub


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



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:

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?
--

Filter & sort
 

I am sorry it has taken me so long to reply back. I see three subs that you
gave me. Do I put all three of these in my current macro? I noticed the one
called Sub Main() says to 'enter your code here. I have a lengthy macro. Do I
need to insert the part of my macro that I want in between saving and
restoring the filter? Am I really confused?
--
Thank you!


"Joel" wrote:

This is the orginal code that compiles without any errors. It is straight
from the microsoft help page except I added the main routine to show you how
to use the functions.

You code was giving errors because you didn't have matched blocks. Some
block types are as following
''''''''''''''''''''''''''''''''
if

end if
''''''''''''''''''''''''''''''''
with sheets("Sheet1")


end with
''''''''''''''''''''''''''''''''''''''''''''''
for i = 1 to 10

next i
''''''''''''''''''''''''''''''''''''''''''''''
do while i < 10

loop
''''''''''''''''''''''''''''''''''''''''''''''''''

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

Sub Main()

ChangeFilters
'enter you r code here
RestoreFilters
End Sub


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



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:

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?
--