Results 1 to 3 of 3

Thread: Shorten and tidy my code?

  1. #1

    Question Shorten and tidy my code?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    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

  3. #3
    Thanks for the help Bob, that has shortened it considerably!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •