Shorten and tidy my code?

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
How can i shorten this code? I think i can shorten the amount of If Else's to just one that does the same thing as what i have got but i dont know how to do this? Take a look.


Sub Filterofcolumn()
'
' Filterofcolumn Macro
'
Sheets("FolderSort").Select
ActiveSheet.Range("A1").Select

If Sheet1.ComboBox1.Value = "M6" Then
Sheets("FolderSort").Select
ActiveSheet.Range("$G$4:$G$368").AutoFilter Field:=1, Criteria1:=Sheet1.ComboBox1.Value
Sheets("FrontPage").Select

ElseIf Sheet1.ComboBox1.Value = "M55" Then
Sheets("FolderSort").Select
ActiveSheet.Range("$G$4:$G$368").AutoFilter Field:=1, Criteria1:=Sheet1.ComboBox1.Value
Sheets("FrontPage").Select

ElseIf Sheet1.ComboBox1.Value = "A66" Then
Sheets("FolderSort").Select
ActiveSheet.Range("$G$4:$G$368").AutoFilter Field:=1, Criteria1:=Sheet1.ComboBox1.Value
Sheets("FrontPage").Select

ElseIf Sheet1.ComboBox1.Value = "A595" Then
Sheets("FolderSort").Select
ActiveSheet.Range("$G$4:$G$368").AutoFilter Field:=1, Criteria1:=Sheet1.ComboBox1.Value
Sheets("FrontPage").Select

ElseIf Sheet1.ComboBox1.Value = "A595" Then
Sheets("FolderSort").Select
ActiveSheet.Range("$G$4:$G$368").AutoFilter Field:=1, Criteria1:=Sheet1.ComboBox1.Value
Sheets("FrontPage").Select

ElseIf Sheet1.ComboBox1.Value = "A590" Then
Sheets("FolderSort").Select
ActiveSheet.Range("$G$4:$G$368").AutoFilter Field:=1, Criteria1:=Sheet1.ComboBox1.Value
Sheets("FrontPage").Select

ElseIf Sheet1.ComboBox1.Value = "A585" Then
Sheets("FolderSort").Select
ActiveSheet.Range("$G$4:$G$368").AutoFilter Field:=1, Criteria1:=Sheet1.ComboBox1.Value
Sheets("FrontPage").Select

ElseIf Sheet1.ComboBox1.Value = "" Then
Sheets("FolderSort").AutoFilterMode = False

End If

End Sub


Thanks in advance!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Code:
Sub Filterofcolumn()'
' Filterofcolumn Macro
'
    
    Select Case Sheet1.ComboBox1.Value
    
        Case "M6", "M55", "A66", "A595", "A590", "A585"
    
            Sheets("FolderSort").Range("$G$4:$G$368").AutoFilter Field:=1, Criteria1:=Sheet1.ComboBox1.Value
        
        Case Else
        
            Sheets("FolderSort").AutoFilterMode = False
    End Select
    
    Sheets("FrontPage").Select
End Sub
 

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
Thanks for the help Bob, that has shortened it considerably!
 
Top