Combining multiple macros into 1?

[Coyotex3]

How can I do this more efficiently? Currently trying to cleanup a bunch of macros I've gathered through this sub and the internet. Wondering if cleaning it up further is possible. I have these subs which are essentially the same with the only difference being the .AutoFilter 1 "*Value*" Is there a way of doing the same thing with one sub?

Sub wyoming()
With ActiveSheet
    .AutoFilterMode = False
    With Range("d1", Range("d" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*wyoming*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

Sub wisconsin()
With ActiveSheet
    .AutoFilterMode = False
    With Range("d1", Range("d" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*wisconsin*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

Sub washington()
With ActiveSheet
    .AutoFilterMode = False
    With Range("d1", Range("d" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*washington*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

Sub virginia()
With ActiveSheet
    .AutoFilterMode = False
    With Range("d1", Range("d" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*virginia"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

 

[tonyyy]

You might consider the following...

Sub States()
Dim States(4) As Variant
Dim i As Long

States(1) = "wyoming"
States(2) = "wisconsin"
States(3) = "washington"
States(4) = "virginia"

With ActiveSheet
    For i = 1 To 4
        .AutoFilterMode = False
        With Range("d1", Range("d" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*" & States(i) & "*"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    Next i
End With
End Sub

Cheers,

Tony

 

[Coyotex3]

You might consider the following...

Sub States()
Dim States(4) As Variant
Dim i As Long

States(1) = "wyoming"
States(2) = "wisconsin"
States(3) = "washington"
States(4) = "virginia"

With ActiveSheet
    For i = 1 To 4
        .AutoFilterMode = False
        With Range("d1", Range("d" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*" & States(i) & "*"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    Next i
End With
End Sub

Cheers,

Tony

Thank you so much! I'm still amazed as to how knowledgeable you guys are in this sub. You guys have truly made our jobs easier by helping us automate a lot of the repetitive stuff.

 

[Coyotex3]

@tonyyy does the "12" in this line: Offset(1).SpecialCells(12).EntireRow.Delete means there are 12 columns?

 

[tonyyy]

"Thank you so much!"
You're very welcome.

"does the "12" in this line: Offset(1).SpecialCells(12).EntireRow.Delete means there are 12 columns?"
The number 12 is an XLCellType enumeration. It's a shorthand way of specifying xlCellTypeVisible.

 

[Coyotex3]

"Thank you so much!"
You're very welcome.

"does the "12" in this line: Offset(1).SpecialCells(12).EntireRow.Delete means there are 12 columns?"
The number 12 is an XLCellType enumeration. It's a shorthand way of specifying xlCellTypeVisible.

Thank you for the link! I'm one of those that does not really understand the basics, still fascinated by macros though.