Not equal to in vba array

[honkin]

In a VBA array, how does one exclude values?

I am trying to autofilter for everything that is not equal to 1, 5 or blank in column 10, but the line of code is obviously incorrect.

.AutoFilter Field:=10, Criteria1:=Array("<>1", "<>5", "<>"), Operator:=xlFilterValues

cheers

 

[Micron]

If this is a sheet array formula you're creating in vba, I don't know how. If it's a vba array as your title suggests, you don't actually see the values, so I think that is not a vba array you're using. However, in case that is the intent, you add items to an array then refer to them by their index number (0 based index). You would have to test an array element and act accordingly
IF aryMyArray(2) <> "5" Then

or loop over the elements using a counter variable.

Please completely disregard if you're dealing with an array in a formula, even if it's being created with vba.

 

[honkin]

If this is a sheet array formula you're creating in vba, I don't know how. If it's a vba array as your title suggests, you don't actually see the values, so I think that is not a vba array you're using. However, in case that is the intent, you add items to an array then refer to them by their index number (0 based index). You would have to test an array element and act accordingly
IF aryMyArray(2) <> "5" Then

or loop over the elements using a counter variable.

Please completely disregard if you're dealing with an array in a formula, even if it's being created with vba.

cheers Micron

Yes, that line is part of about 5 other autofilter criteria; all the rest are filtering for = and many are single value, with a couple being arrays. It's always easy to filter for when things are equal to, but on this line, there are so many possible numbers in column J (10) so thought it was easier to just try and exclude the 3 not required.

Ta

 

[kevin9999]

As far as I know, you can't exclude more than 2 criteria using AutoFilter. There are workarounds however. In the method below, you use a dictionary to create a unique set of all values in column 10, then go through the keys - removing any that are 1 or 5. Finally you use the remaining keys as your array to filter on column 10 (blanks are excluded by default).

Option Explicit
Sub Exclude_3()
    Dim d As Object, c As Range, tmp As String, x, i As Long, ws As Worksheet
    Set ws = ActiveSheet
    Set d = CreateObject("scripting.dictionary")
    x = Application.Transpose(Range("J2", Cells(Rows.Count, "J").End(xlUp)))
    
    For i = 1 To UBound(x, 1)
        d(x(i)) = 1
    Next

    For Each c In ws.Range("J2", Cells(Rows.Count, "J").End(xlUp)) '<< change Column/Range to suit
        tmp = c.Value
        If d.exists(tmp) And (tmp) = "1" Then d.Remove (tmp) '<< removes 1
        If d.exists(tmp) And (tmp) = "5" Then d.Remove (tmp) '<< removes 5
    Next
    
    With ws.Cells(1, 1).CurrentRegion
        .AutoFilter 10, Array(d.keys), 7
    End With
End Sub

Before:

filter 3.xlsb
	A	B	C	D	E	F	G	H	I	J
1	header1	header2	header3	header4	header5	header6	header7	header8	header9	header10
2	data	data	data	data	data	data	data	data	data	1
3	data	data	data	data	data	data	data	data	data	5
4	data	data	data	data	data	data	data	data	data
5	data	data	data	data	data	data	data	data	data	a
6	data	data	data	data	data	data	data	data	data	b
7	data	data	data	data	data	data	data	data	data	c
8	data	data	data	data	data	data	data	data	data	1
9	data	data	data	data	data	data	data	data	data	5
10	data	data	data	data	data	data	data	data	data
11	data	data	data	data	data	data	data	data	data	x
12	data	data	data	data	data	data	data	data	data	y
13	data	data	data	data	data	data	data	data	data	z
14
Sheet1

After:

filter 3.xlsb
	A	B	C	D	E	F	G	H	I	J
1	header1	header2	header3	header4	header5	header6	header7	header8	header9	header10
5	data	data	data	data	data	data	data	data	data	a
6	data	data	data	data	data	data	data	data	data	b
7	data	data	data	data	data	data	data	data	data	c
11	data	data	data	data	data	data	data	data	data	x
12	data	data	data	data	data	data	data	data	data	y
13	data	data	data	data	data	data	data	data	data	z
14
Sheet1

 

[honkin]

As far as I know, you can't exclude more than 2 criteria using AutoFilter. There are workarounds however. In the method below, you use a dictionary to create a unique set of all values in column 10, then go through the keys - removing any that are 1 or 5. Finally you use the remaining keys as your array to filter on column 10 (blanks are excluded by default).

Option Explicit
Sub Exclude_3()
    Dim d As Object, c As Range, tmp As String, x, i As Long, ws As Worksheet
    Set ws = ActiveSheet
    Set d = CreateObject("scripting.dictionary")
    x = Application.Transpose(Range("J2", Cells(Rows.Count, "J").End(xlUp)))
   
    For i = 1 To UBound(x, 1)
        d(x(i)) = 1
    Next

    For Each c In ws.Range("J2", Cells(Rows.Count, "J").End(xlUp)) '<< change Column/Range to suit
        tmp = c.Value
        If d.exists(tmp) And (tmp) = "1" Then d.Remove (tmp) '<< removes 1
        If d.exists(tmp) And (tmp) = "5" Then d.Remove (tmp) '<< removes 5
    Next
   
    With ws.Cells(1, 1).CurrentRegion
        .AutoFilter 10, Array(d.keys), 7
    End With
End Sub

Before:

filter 3.xlsb
	A	B	C	D	E	F	G	H	I	J
1	header1	header2	header3	header4	header5	header6	header7	header8	header9	header10
2	data	data	data	data	data	data	data	data	data	1
3	data	data	data	data	data	data	data	data	data	5
4	data	data	data	data	data	data	data	data	data
5	data	data	data	data	data	data	data	data	data	a
6	data	data	data	data	data	data	data	data	data	b
7	data	data	data	data	data	data	data	data	data	c
8	data	data	data	data	data	data	data	data	data	1
9	data	data	data	data	data	data	data	data	data	5
10	data	data	data	data	data	data	data	data	data
11	data	data	data	data	data	data	data	data	data	x
12	data	data	data	data	data	data	data	data	data	y
13	data	data	data	data	data	data	data	data	data	z
14
Sheet1

After:

filter 3.xlsb
	A	B	C	D	E	F	G	H	I	J
1	header1	header2	header3	header4	header5	header6	header7	header8	header9	header10
5	data	data	data	data	data	data	data	data	data	a
6	data	data	data	data	data	data	data	data	data	b
7	data	data	data	data	data	data	data	data	data	c
11	data	data	data	data	data	data	data	data	data	x
12	data	data	data	data	data	data	data	data	data	y
13	data	data	data	data	data	data	data	data	data	z
14
Sheet1

Cheers Kevin
A quick question; does this actually remove the rows with 1, 5 & blank, or simply hide them?

 

[kevin9999]

It just hides them. The AutoFilter is actually on, you just can't see the down arrows with the XL2bb mini-sheet. You can remove them as well if you want.

 

[honkin]

It just hides them. The AutoFilter is actually on, you just can't see the down arrows with the XL2bb mini-sheet. You can remove them as well if you want.

Then it seems a perfect workaround mate. Cheers. I'll copy and paste it and see how it goes. Ta!