Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort/Filter | Excel Discussion (Misc queries) | |||
sort and filter | Excel Discussion (Misc queries) | |||
Filter or Sort?? | Excel Programming | |||
Filter & Sort | Excel Worksheet Functions | |||
Filter, sort, filter and then display | Excel Programming |