有没有一种方法不能在Excel VBA中使用条件数组? [英] Is there a way to NOT a criteria array in Excel VBA?

问题描述

我有一列具有已知值的列.5是用石头写的，奇数是新的.我需要过滤新的(未知)值.如果我只有2个已知值，则可以过滤出Criteria1&2通过使用=<>"

I have a column with known values. 5 are written in stone, and the odd one is new. I need to filter on the new (unknown) value. If I only had 2 known values, I could filter out Criteria1 & 2 by using ="<>"

由于我们无法使用Criteria3，4，...有没有办法使用类似的东西:

Since we can't use Criteria3, 4, ... Is there a way to use something like:

Criteria1:=<>" Array("Val1"，"Val2"，...)

Criteria1:="<>"Array("Val1", "Val2", ...)

(澄清:我想不显示数组中的所有内容.)

(clarification: I want to show everything NOT in the array.)

谢谢.

推荐答案

数据如下:

我们希望看到所有除了 Val1 ， Val2 和 Val3

We want to see everything except Val1,Val2, and Val3

这是一种方法:

Sub AllButThree()
   Dim N As Long, ary(), c As Collection
   Dim i As Long, a As String, v As String
   ary = Array("Val1", "Val2", "Val3")
   Set c = New Collection
   N = Cells(Rows.Count, "A").End(xlUp).Row

   On Error Resume Next
   For i = 2 To N
      v = Cells(i, 1).Value
      If v <> ary(0) And v <> ary(1) And v < ary(2) Then
         c.Add v, CStr(v)
      End If
   Next i

   On Error GoTo 0
   ReDim bry(1 To c.Count)
   For i = 1 To c.Count
      bry(i) = c.Item(i)
   Next i

   ActiveSheet.Range("$A$1:$A" & N).AutoFilter Field:=1, Criteria1:=(bry), Operator:=xlFilterValues
End Sub

它构建了我们想要看到的数组.

It builds an array of what we want to see.

这篇关于有没有一种方法不能在Excel VBA中使用条件数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！