Advice on VBA code (Excel)

[Rudi]

Hi,

Is the flow (or logic) of the code OK?
I need to update the filter and MailAddr variable with each loop, and then execute the code below the loop to filter and email the worksheet.
I just wonder about the way I exit the sub... Its just so sudden and I don't even end the Select block?

If this is OK...I'm fine with it and it works, but just querying if my flow is OK or if there is a better way of doing this?
TX

Code: Select all

Sub ApplyFilter()
Dim i As Integer

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    shOrig = ActiveSheet.Name
    
    'Loop count is (of email list count + 1) in order to exit sub at highest Case #
    For i = 1 To 10
        Select Case i
        Case 1
            ActiveSheet.AutoFilterMode = False
            MailAddr = "test@test.com; test2@test.com"
        Case 2
            ActiveSheet.Range("A3").CurrentRegion.AutoFilter _
                    Field:=8, Criteria1:=Array("CF", "GE", "O2"), Operator:=xlFilterValues
            MailAddr = "test3@test.com; test4@test.com"
        Case 3
            'Filter
            'Update MailAddr variable
        Case 4
            '...
        Case 5
            '...
        Case 6
            '...
        Case 7
            '...
        Case 8
            '...
        Case 9
            '...
        Case 10
            Application.ScreenUpdating = True
            Application.EnableEvents = True
            Exit Sub
        End Select

        Range("A3").CurrentRegion.Offset(-2).Resize(Range("A3").CurrentRegion.Rows.Count + 2).Copy
        Sheets.Add After:=ActiveSheet
        Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
        Range("A1").PasteSpecial Paste:=xlPasteAll
        Application.CutCopyMode = False
        Range("A1").Select
        ActiveSheet.Name = shOrig & " - " & Format(Date, "yyyymmdd")

        SendSections 'Call another Sub to send list via Outlook email

        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Range("A1").Select
        ActiveSheet.AutoFilterMode = False
    Next i
End Sub

[StuartR]

I usually set a boolean variable that says it's time to exit and then test this variable later in the code with an If statement. I don't suppose it's any better, but it feels better structured to me.

Would it not be even simpler to replace
For i = 1 To 10
with
For 1 = 1 to 9

and then put your cleanup code after the loop

[Rudi]

Would it not be even simpler to replace
For i = 1 To 10
with
For 1 = 1 to 9

and then put your cleanup code after the loop

That is simpler...TX.
I think this is the issue that made me question the logic initially. It was as simple as that.... LOL (how these simple issues can elude one!!!)

Cheers