Always include certain cells regardless of filter: VBAsolution

Question

I have a table that has 5 sections of rows (1 for each day of the workweek). Each row header is a staff member's name- there are about 15 people, so 15*5 rows in total. Interspersed are rows titled "Monday" "Tuesday" etc. The column header is called "Name"

Below is the code for the search box:

 Sub ScheduleSearch()
    'PURPOSE: Filter Data by Staff Name

Dim myButton As OptionButton
Dim MyVal As Long
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant

'Load Sheet into A Variable
  Set sht = ActiveSheet

'Unfilter Data (if necessary)
  On Error Resume Next
    sht.ShowAllData
  On Error GoTo 0

'Filtered Data Range (include column heading cells)
  Set DataRange = sht.ListObjects("Schedule").Range 'Table

'Retrieve User's Search Input
  mySearch = sht.Shapes("WireSearch").TextFrame.Characters.Text 'Control Form


'Loop Through Option Buttons
  For Each myButton In ActiveSheet.OptionButtons
      If myButton.Value = 1 Then
        ButtonName = myButton.Text
        Exit For
      End If
  Next myButton

'Determine Filter Field
  On Error GoTo HeadingNotFound
    myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
  On Error GoTo 0

'Filter Data
  DataRange.AutoFilter _
    Field:=myField, _
    Criteria1:="=*" & mySearch & "*", _
    Operator:=xlAnd

'Clear Search Field
  sht.Shapes("WireSearch").TextFrame.Characters.Text = "" 'Control Form
  'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
  'sht.Range("A1").Value = "" 'Cell Input

Exit Sub

'ERROR HANDLERS
HeadingNotFound:
  MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
    vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"

End Sub

Sub ClearScheduleFilter()
'
' ClearScheduleFilter Macro
'

'
    ActiveSheet.ListObjects("Schedule").Range.AutoFilter Field:=1
End Sub

Right now, when I search for Person A, the filter works perfectly and filters the column "Name" by Person A.

However- I want the cells titled "Monday" "Tuesday" "Wednesday" "Thursday" and "Friday" to automatically also be included in this filter no matter what the user searches.

Is there a code for this?

UPDATE: the following code filters the days and week row however it does not filter the mySearch value. Any idea why?

'Filter Data
   DataRange.AutoFilter _
   Field:=myField, _
   Criteria1:=Array("MONDAY:", "TUESDAY:", "WEDNESDAY:", "THURSDAY:", "FRIDAY:", "WEEK:", " & mySearch & "), _
   Operator:=xlFilterValues

Answer 1

For the name field, use an Array list to set the filter that includes all the days of the week and the person.

A basic shell is this:

Selection.AutoFilter 1, Array("MONDAY", "TUESDAY" , "WEDNESDAY" , "A"), xlFilterValues

For the code you've provided this should work:

 DataRange.AutoFilter _
    Field:=myField, _
    Criteria1:= Array("MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY"," & mySearch & ")", _
    Operator:=xlFilterValues