Displaying Repeated Advanced Filter Results

Z

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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Datsmart

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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
Z

ZSauce

Board Regular
Joined
Oct 31, 2005
Messages
64
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
Datsmart

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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
Z

ZSauce

Board Regular
Joined
Oct 31, 2005
Messages
64
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
Datsmart

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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
Z

ZSauce

Board Regular
Joined
Oct 31, 2005
Messages
64
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
Z

ZSauce

Board Regular
Joined
Oct 31, 2005
Messages
64
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
Z

ZSauce

Board Regular
Joined
Oct 31, 2005
Messages
64
John,

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

Thank you kindly for all of your help.
 
Upvote 0
Datsmart

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
If you have row 1 frozen, then use "Range("A2").Select". That will get you to the top of the page.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Expand and collapse menu via filter on separate sheets
Replies
1
Views
193
Alex Blakenburg
A
M
  • Question
JIRA to Excel (list of issues): csv download to excel
Replies
2
Views
338
Mike_R_
M
M
  • Question
Cell notes following my cell selection
Replies
1
Views
315
Mrock1
M
A
  • Question
Problem With My Loop(s) Being Able To Match Data?
Replies
2
Views
125
Ark68
A
V
  • Solved
VBA copy filtered range
Replies
8
Views
249
Valentino
V

Forum statistics

Threads
1,191,416
Messages
5,986,430
Members
440,029
Latest member
GabSSSH

Share this page

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
Top