Macro help

[Alvaroro84]

Im using this code I found here to get rid of these criteria AZ,ME,CA,NY. in column "I" I however cant get it to work. can any one provide me with some assistance

   'apply a filter, row 1 contains my titles
    Range("A1:L1").Select
    Selection.AutoFilter
 
    Dim filterCriteria() As String
    Dim count As Long, secondArray As Variant
    Dim L As Long, c As String, k As String, rowNumb As Long
   
    secondArray = Array("AZ", " ME", " CA", " NY")
   
    c = 0
    k = 0
    count = 0
    rowNumb = ActiveSheet.Range(ActiveSheet.Range("I2"), ActiveSheet.Range("I1").End(xlDown)).Rows.count
   
    For L = 1 To rowNumb
        c = ActiveSheet.Range("I1").Offset(L)
        If c <> k Then
            'check the current activity type against the array of types we don’t want. If it isn’t in the array we add it to an array that will be used as the filter criteria
            If UBound(Filter(secondArray, c)) = -1 Then
                ReDim Preserve filterCriteria(0 To count)
                filterCriteria(count) = c
                count = count + 1
            End If
 
            k = c
        End If
    Next
   
    With ActiveSheet
        .Range(.Range("A1"), .Range("A1").End(xlDown).Offset(0, 11)).AutoFilter Field:=9, Criteria1:=filterCriteria, Operator:=xlFilterValues
    End With

 

[Logit]

The purpose of the code is to "filter out" those abbreviations. The code as iswill not completely delete them
from the sheet.

 

[Alvaroro84]

Sorry my wording is confusing. That is what I ment. When I run the macro those abbreviations aren't filtered out. Is there something that I changed that was incorrect. This is for columb I

 

[Logit]

Not certain why it doesnt work there. It runs fine here.

Option Explicit Sub DelStates() 'apply a filter, row 1 contains my titles Range("A1:L1").Select Selection.AutoFilter Dim filterCriteria() As String Dim count As Long, secondArray As Variant Dim L As Long, c As String, k As String, rowNumb As Long secondArray = Array("AZ", " ME", " CA", " NY") c = 0 k = 0 count = 0 rowNumb = ActiveSheet.Range(ActiveSheet.Range("I2"), ActiveSheet.Range("I1").End(xlDown)).Rows.count For L = 1 To rowNumb c = ActiveSheet.Range("I1").Offset(L) If c <> k Then 'check the current activity type against the array of types we don’t want. If it isn’t in the array we add it to an array that will be used as the filter criteria If UBound(Filter(secondArray, c)) = -1 Then ReDim Preserve filterCriteria(0 To count) filterCriteria(count) = c count = count + 1 End If k = c End If Next With ActiveSheet .Range(.Range("A1"), .Range("A1").End(xlDown).Offset(0, 11)).AutoFilter Field:=9, Criteria1:=filterCriteria, Operator:=xlFilterValues End With End Sub [/CODE]

 

[Alvaroro84]

My table goes all the way to column AJ and row 872,678 could that be it? After running it I do a control F in column "I"and I still find all those criteria In the column.

 

[Logit]

Shouldnt make any difference.

 

[Alvaroro84]

I ran it again and this time it only filtered CA

 

[Alvaroro84]

This time I looked up the states manually and change NY to NY and did it to all the cells that contain NY and ran my macro again and it worked. Ideally I would like not to do that

 

[Fluff]

Do you have spaces before the state in the worksheet?

 

[Alvaroro84]

I don't think so. I have another macro that looks like this and it identifies all of them so that's why I'm confused as to why the other macro can't identify them

.Range("A1").AutoFilter 9, Array("DE", "AK", "MN", "ND", "NE", "OR", "SD", "WA", "WI"), xlFilterValues