Displaying Repeated Advanced Filter Results

ZSauce

Board Regular
Joined
Oct 31, 2005
Messages
64
I created a user form that provides a menu that allows users to perform advanced filtering. They can also scroll through the results freely. A problem is that a subsequent advanced filter selection does not always display the top row of filtered records, depending upon what the user has chosen to display prior to selecting the next set of filtered records.

How can I make the top row of filtered records always be displayed so that users will not overlook results of advanced filter operations?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
How are you choosing your data region prior to running the advanced filter? By code? If so can you post the code?

Are you filtering in place?
I prefer to use advanced filter and filter to another location. That way you do not have to worry about messing up the original data.
 
Upvote 0
John,

Yes, I am filtering in place. Here's my code:

Option Explicit

Private Sub CancelButton_Click()
Unload UserForm1
End Sub
Private Sub ToggleButton1_Click()
If ToggleButton1.Caption = "Show All Variances" Then
ToggleButton1.Caption = "Show Non-zero Variances"
Else
ToggleButton1.Caption = "Show All Variances"
End If
End Sub
Private Sub OKButton_Click()
If ToggleButton1.Caption = "Show All Variances" Then
If OptionODO Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("ODOCriteria")
ElseIf OptionDLA Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("DLACriteria")
ElseIf OptionDeCA Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("DeCACriteria")
ElseIf OptionDFAS Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("DFASCriteria")
ElseIf OptionDISA Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("DISACriteria")
ElseIf OptionNAVY Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("NAVYCriteria")
Else:
If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData: Unload UserForm1
End If
Else:
If OptionODO Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("ODOnonzeroCriteria")
ElseIf OptionDLA Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("DLAnonzeroCriteria")
ElseIf OptionDeCA Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("DeCAnonzeroCriteria")
ElseIf OptionDFAS Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("DFASnonzeroCriteria")
ElseIf OptionDISA Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("DISAnonzeroCriteria")
ElseIf OptionNAVY Then
Range("A1").Select
ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("NAVYnonzeroCriteria")
Else: ActiveCell.CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Branch Criteria").Range("AllnonzeroCriteria")
End If
End If
End Sub
 
Upvote 0
A couple of your Range setups do not include A1 in your data range.
Try this revised code:
Code:
Private Sub OKButton_Click()
If ToggleButton1.Caption = "Show All Variances" Then
    If OptionODO Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("ODOCriteria")
    ElseIf OptionDLA Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("DLACriteria")
    ElseIf OptionDeCA Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("DeCACriteria")
    ElseIf OptionDFAS Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("DFASCriteria")
    ElseIf OptionDISA Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("DISACriteria")
    ElseIf OptionNAVY Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("NAVYCriteria")
    Else:
        If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData: Unload UserForm1
    End If
    Else:
    If OptionODO Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("ODOnonzeroCriteria")
    ElseIf OptionDLA Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("DLAnonzeroCriteria")
    ElseIf OptionDeCA Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("DeCAnonzeroCriteria")
    ElseIf OptionDFAS Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("DFASnonzeroCriteria")
    ElseIf OptionDISA Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("DISAnonzeroCriteria")
    ElseIf OptionNAVY Then
        Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("NAVYnonzeroCriteria")
    Else: Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Branch Criteria").Range("AllnonzeroCriteria")
    End If
End If
End Sub
 
Upvote 0
John,

Thank you for tightening the code. However, I still have the same basic problem in that the top rows of filtered results will sometimes require the users to scroll or cursor up the screen to see them. If they fail to see these records, they can easily overlook enormous dollar amounts of variances.
 
Upvote 0
If the only problem is that they can not "see" the upper rows, add a select statement at the end of your code like, "Range("A1").Select"
 
Upvote 0
John,

Because I have the first row frozen, the Range("A1").Select idea doesn't work. If I take off the "freeze panes" while users are viewing the filtered data, the column headings will scroll off the screen.

If I could make Excel perform a Control-Home key press in the active sheet, that would do the trick. I tried some variations of SendKeys, but I haven't met with success.
 
Upvote 0
John,

If I place "ActiveWindow.LargeScroll up:=10" at appropriate places in the code, it looks like I will get the effect I want. The "10" parameter is arbitrary, so I will try to make this dynamic to accommodate the number of total records that could appear in the data.
 
Upvote 0
John,

I'm going to go with "ActiveWindow.ScrollRow = 2".

Thank you kindly for all of your help.
 
Upvote 0
If you have row 1 frozen, then use "Range("A2").Select". That will get you to the top of the page.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top