A quick way to filter

[itsgrady]

I have a large table of 25,000+ rows. This data is updated throughout the day. I have to “Filter” the document each day by one of the columns “Inb Pro”. For the most part, the filtering is the same each day, but I do have to add new filter each week.

I have to choose 25 to 30 filters each day and there is room for errors. Is there way can automate this task through VBA or some other method.

 

[Jeffrey Mahoney]

YES. What I have done on a current WB I manage is to create a pulldown that allows me to choose which filter I want to run. The data validation uses the table below.

Time-Phase and Submit Checker_CWF.xlsm
	B	C	D
1	List of Checks
2	Num	Description	Macro
3	0.1	0.1 Showing most recent time phased at top	Recent
4	0.2	0.2 Default View	Defaults
5	1.0	1.0 Did not submit after last time phase	Check_1
6	2.0	2.0 Had actual and/or forecast in current month, needs attention	Check_2
7	3.0	3.0 Check blanks, verify no missed TimePhase/Submit	Check_3
8	4.0	4.0 Compare Cur Month Forecast to Cur Month ACWP	Check_4
9	5.0	5.0 Check for Undistributed EAC	Check_5
10	6.0	6.0 Check for Costs Hitting After Last Forecast Submittal	Check_6
11
12
13
SETUP

These macros when I choose one of the items

Private Sub RunCheck_btn_Click()
  
  Dim CheckItm As String
  Dim CheckList As Range
  Dim CheckSub As String
  Dim Itm As Long
  
  
  CheckItm = Range("Check_Comment").Value
  Set CheckList = SETUP.Range("CheckList")
  Itm = Application.WorksheetFunction.Match(CheckItm, CheckList, 1)
  CheckSub = CheckList.Resize(1, 1).Offset(Itm - 1, 1)
  
  Application.Run CheckSub
  
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Not Intersect(Target, Range("Check_Comment")) Is Nothing Then
    RunCheck_btn_Click
    Range("Check_Comment").Select
  End If
  
End Sub

This is one of the Filter Subs that get called

'1.0 Did not submit after last time phase
Sub Check_1()
  
  Dim Fld1 As Long
  Dim Fld2 As Long
  
  Fld1 = GetFieldNum(Range("CompTbl[#Headers]"), "Time Phase vs Submittal")
  
  Application.ScreenUpdating = False
  
  Range("$A$4").Select
  Clear_Filter
  Comp.ListObjects("CompTbl").Sort.SortFields.Clear
  Comp.ListObjects("CompTbl").Sort.SortFields.Add2 Key:=Range("CompTbl[[#All],[Time Phase vs Submittal]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    With Comp.ListObjects("CompTbl").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Comp.ListObjects("CompTbl").Range.AutoFilter Field:=Fld1, Criteria1:="Bad"

  'ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
  'Range("Check_Comment").Value = "1.0 Did not submit after last time phase"
  CursorHome
  Application.ScreenUpdating = True
End Sub

 

[itsgrady]

Thanks for the reply. Looks very interesting. How easy is it to add a new criteria for the end user?

 

[Jeffrey Mahoney]

It is currently setup to run the same filters every time. An end user would not like to edit this.

For future iterations it would be cool to have a table allowing multiple criteria choices for each selection made.