Hi again guys, Looking for a little help in tweaking a pre written macro someone kindly donated to me

Basically I have a spreadsheet which contains a "master import" worksheet in which I have column headers on row 1 which never change. Every month we run a report and overwrite the data on the master import sheet with fresh data (of the same format).

Column E of the spreadsheet is a field called "BU" and each cell in the column usually contains one of the following

ABC
AI
AOS
AVRIV
AVSHIV
DELTAL
GROUP
HIB
RIC
RBJV
UKYU
UKGO
UKLP

There is the possibility however that the column could contain something other than one of those acronyms listed above.

The following autofilter in essence is an advanced filter which looks at the "master import" worksheet data. Looks down column E ("BU") and applies an advanced filter which displays only those rows where column "BU" contains something other than the pre defined acronyms above.

This works perfectly well in most cases, however I have found that if the data I paste into the master import sheet does NOT contain an entry in column E for anything other than the following

ABC
AI
AOS
AVRIV
AVSHIV
DELTAL
GROUP
HIB
RIC
RBJV
UKYU
UKGO
UKLP

Instead of displaying a friendly message like "No incorrect BU's found", I just get an error message stating

Run-time error '5'
Invalid procedure call or argument.

And the macro is highlights in yellow the following line

"ActiveSheet.Range("B:T").autofilter Field:=4, Criteria1:=arrCriteria, Operator:=xlFilterValues"

Just wondering if anyone would be kind enough to edit the below macro so that in these instances, a friendly error message is displayed like "No incorrect BU's found" instead of the macro failing outright.

Many thanks in advance



Macro details..

Sub autofilter()
'create the ignore array
'to add items increaset the value of x in "arrIgnore(1 to x)" and add a new arrIgnore(x + 1) = "whatever"
Dim arrIgnore(1 To 14) As String
arrIgnore(1) = "" 'non blank
arrIgnore(2) = "ABC"
arrIgnore(3) = "AI"
arrIgnore(4) = "AOS"
arrIgnore(5) = "AVRIV"
arrIgnore(6) = "AVSHIV"
arrIgnore(7) = "DELTAL"
arrIgnore(8) = "GROUP"
arrIgnore(9) = "HIB"
arrIgnore(10) = "RIC"
arrIgnore(11) = "RBJV"
arrIgnore(12) = "UKYU"
arrIgnore(13) = "UKGO"
arrIgnore(14) = "UKLP"

Dim arrUnique() As String
Dim rngBUlist As Range
Dim rngUnique As Range
Dim strTest As String
Dim intUnique As Integer

Application.ScreenUpdating = False
Sheet1.Select

Set rngBUlist = Sheet1.Range("E1:E" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row)

Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUnique = rngBUlist.SpecialCells(xlCellTypeVisible)

intUnique = 0
For Each myRow In rngUnique.Rows
If myRow.Cells(1).Value = "BU" Then
'ignore it
Else
intUnique = intUnique + 1
ReDim Preserve arrUnique(1 To intUnique)
arrUnique(intUnique) = myRow.Cells(1).Value
End If
Next
'now got an array of all the unique BUs present in the sheet

'now populate our criteria array without anything in the ignore array
Dim inIgnore As Boolean
inIgnore = False
Dim arrCriteria() As String
Dim intCriteria As Integer
intCriteria = 0

For i = UBound(arrUnique) To 1 Step -1
inIgnore = False
For j = 1 To UBound(arrIgnore) Step 1
If arrUnique(i) = arrIgnore(j) Then
inIgnore = True
Exit For
End If
Next

If inIgnore = False Then
intCriteria = intCriteria + 1
ReDim Preserve arrCriteria(1 To intCriteria)
arrCriteria(intCriteria) = arrUnique(i)
End If
Next

'now we can use arrCriteria to do the filter
ActiveSheet.Range("B:T").autofilter Field:=4, Criteria1:=arrCriteria, Operator:=xlFilterValues

Application.ScreenUpdating = True

End Sub