Filter with VBA in excel

Question

I am trying to filter my movie list. Unfortunately it doesn't work out so well yet. I found a very fast way, but this one is missing a few options. If I read the entire column into an array and search for the individual words, it takes a relatively long time for over 2000 movies. What I miss: In column A I can only filter by the first word. So it only goes from the beginning of the title. For example, "F" finds all "Film*" titles.

In column B and C I would like to be able to sort "from to". So all movies after 2012 and for example all with a rating better than 7.

In column G and H I have again the problem that I can only sort from the front. So if the genre Action is in second place, I can't find it. Additional i want to find 2 genres for example:"* Crime * Action*"

What works well is that I can now already combine.

The Excel Sheet:

https://mega.nz/file/RsUXRKgD#4ba95fkQOYiteWCH8WST8AuSKZi0k6YCtkuJkOK8tQc

My Code:

'filter in row2
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
    
    If Not Application.Intersect(Range("A2:G2"), Range(Target.Address)) Is Nothing Then
        If Cells(2, 1).Value = "" And Cells(2, 2).Value = "" And Cells(2, 3).Value = "" And Cells(2, 4).Value = "" And Cells(2, 5).Value = "" And Cells(2, 6).Value = "" And Cells(2, 7).Value = "" Then
            On Error Resume Next
            ActiveSheet.ShowAllData
            ActiveSheet.Rows.Hidden = False
        Else
            LR = UsedRange.Rows.Count  'includes hidden rows
            Range("A1:G" & LR).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:G3")
        End If
    End If

End Sub 



Private Sub ToggleButton1_Click()
Dim Reihe As String
Reihe = "A"
If ToggleButton1.Value = False Then
Call orderXA(Reihe)
Else
Call orderXD(Reihe)
End If

End Sub
Private Sub ToggleButton2_Click()
Dim Reihe As String
Reihe = "B"
If ToggleButton2.Value = False Then
Call orderXA(Reihe)
Else
Call orderXD(Reihe)
End If
End Sub

Private Sub ToggleButton3_Click()
Dim Reihe As String
Reihe = "C"
If ToggleButton3.Value = False Then
Call orderXA(Reihe)
Else
Call orderXD(Reihe)
End If
End Sub

Private Sub ToggleButton4_Click()
Dim Reihe As String
Reihe = "D"
If ToggleButton4.Value = False Then
Call orderXA(Reihe)
Else
Call orderXD(Reihe)
End If
End Sub

Private Sub ToggleButton5_Click()
Dim Reihe As String
Reihe = "E"
If ToggleButton5.Value = False Then
Call orderXA(Reihe)
Else
Call orderXD(Reihe)
End If
End Sub

Private Sub ToggleButton6_Click()
Dim Reihe As String
Reihe = "F"
If ToggleButton6.Value = False Then
Call orderXA(Reihe)
Else
Call orderXD(Reihe)
End If
End Sub

Private Sub ToggleButton7_Click()
Dim Reihe As String
Reihe = "G"
If ToggleButton7.Value = False Then
Call orderXA(Reihe)
Else
Call orderXD(Reihe)
End If
End Sub
   

Sub orderXA(Reihe As String)
LR = UsedRange.Rows.Count  'zählt uf versteckte mit
Range("A3:H" & LR).Sort Key1:=Range(Reihe & "4"), order1:=xlAscending, Header:=xlYes

End Sub
Sub orderXD(Reihe As String)
LR = UsedRange.Rows.Count  'zählt uf versteckte mit
Range("A3:H" & LR).Sort Key1:=Range(Reihe & "4"), order1:=xlDescending, Header:=xlYes

End Sub

Answer 1

Why so complicated? Just put on Autofilter. You don't even need VBA for that.

Sorry for the german screenshot.