arrays - Excel VBA删除给定索引具有混合值的行

Question

我有以下数据

Name     ID    Value

Alice    12C    500

Bob      14     60

Dan       15C    64

Dan       1C    25

Alice    4       556

Bob      11     455

在我的数据中，Alice 既有数字 (4) 又有字符串+数字 ID (12C)，我想删除所有 Alice 行，同时我想保留其 ID 严格为数字的名称数据(Bob 11、14)或严格的字符串+数字(Dan 15C，1C)。

首先，我制作了一组唯一的 Name 条目:

   FinalRow = 7
   Name_column = 1
   n = 1

     Dim FID_Array() As Variant

          ReDim Preserve FID_Array(1 To 1)
          FID_Array(1) = Cells(2, Name_column)

     For j = 3 To FinalRow

        If Cells(j, Name_column).Value <> FID_Array(n) Then
                 ReDim Preserve FID_Array(1 To n + 1)
                 FID_Array(n + 1) = Cells(j, Name_column).Value
                 n = n + 1


        End If
     Next j

然后我制作一个包含特定名称的行号数组

ReDim Preserve Count_FID_Array(1 To 1) As Variant
  n = 1
  range_FID = A2:A7


' In my actual code this is Range_FID
' range_FID = Cells(2, FolderId_column).Address & ":" & Cells(FinalRow,  FolderId_column).Address

   For Each itm5 In FID_Array()

           Count_FID_Array(n) = Application.CountIf(" & range_FID & ", " & itm5 & ")
           ReDim Preserve Count_FID_Array(1 To n + 1)
           n = n + 1

    Next itm5

我认为我的 CountIf 不起作用。我试图将 Count_FID_Array 的值存储在不同工作表的另一个单元格中，但我得到了#value!

如果我让 countIf 工作，那么我将按名称对数据进行排序，然后在接下来的“n”次双循环中检查 ID 变量，以查看最后一个数字是否全部为“C”或检查是否他们的 ID 都是数字。

您能否指出为什么我的 countif 不起作用，是否有更聪明的方法来做到这一点？

我在这里使用名称数组，因为最后我想将数组输入自动过滤器并删除我不想要的行。

2013 年 11 月 21 日下午 3:45 更新 1:我已经解决了这个问题:

我基本上创建了三列。第一列是 0 还是 1，具体取决于 ID 是否全是数字。第二列是 0 还是 1，具体取决于最后一个数字是“C”(在我的实际工作中，最后两个数字是“IB”)，最后我将这些出现的频率与名称本身的频率进行了比较。如果其中任何一个匹配，那么我给它数字 1，否则为 0。我稍后使用这个索引来自动过滤。

现在我将尝试在 VBA 代码中使用 zx8754 的较短公式，并尝试解决 Joe 提出的有关 Countif 的问题。

子文件夹的子条件()

  FinalColumn = Cells(1, Columns.Count).End(xlToLeft).Column
  FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
  ActiveWorkbook.ActiveSheet.Columns(FinalColumn + 1).Insert
  ActiveWorkbook.ActiveSheet.Columns(FinalColumn + 2).Insert
  ActiveWorkbook.ActiveSheet.Columns(FinalColumn + 3).Insert

  Isnumber_Column = FinalColumn + 1
  Is_IB_Column = FinalColumn + 2
  Exceptions_Column = FinalColumn + 3
  Cells(1, Isnumber_Column) = "Number"
  Cells(1, Is_IB_Column) = "Letters"
  Cells(1, Exceptions_Column) = "Exceptions"

  For j = 1 To FinalColumn
     If Cells(1, j).Value = "TradeId" Then
         TradeId_column = j
     ElseIf Cells(1, j).Value = "Total Notional per folder" Then
         Total_Notional_Per_Folder_Column = j
     ElseIf Cells(1, j).Value = "ExternalId" Then
         ExternalId_Column = j
      ElseIf Cells(1, j).Value = "FolderId" Then
         FolderId_column = j

     End If
   Next j
   range_FolderId_fixed = Cells(2, FolderId_column).Address & ":" & Cells(FinalRow, FolderId_column).Address
   range_TradeId_fixed = Cells(2, TradeId_column).Address & ":" & Cells(FinalRow, TradeId_column).Address
   range_Isnumber = Cells(2, Isnumber_Column).Address & ":" & Cells(FinalRow, Isnumber_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
   range_Isnumber_fixed = Cells(2, Isnumber_Column).Address & ":" & Cells(FinalRow, Isnumber_Column).Address
   range_Is_IB = Cells(2, Is_IB_Column).Address & ":" & Cells(FinalRow, Is_IB_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
   range_Is_IB_fixed = Cells(2, Is_IB_Column).Address & ":" & Cells(FinalRow, Is_IB_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
   range_FolderId_cell = Cells(2, FolderId_column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
   range_TradeId_cell = Cells(2, TradeId_column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
   range_Exceptions = Cells(2, Exceptions_Column).Address & ":" & Cells(FinalRow, Exceptions_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)


   Range(range_Isnumber).Formula = "=Isnumber(" & range_TradeId_cell & ")*1"
   Range(range_Is_IB).Formula = "=(RIGHT(" & range_TradeId_cell & ",2)= ""IB"")*1"

   Range(range_Exceptions).Formula = "=(SUMIF(" & range_FolderId_fixed & "," & range_FolderId_cell & "," & range_Isnumber_fixed & ")= COUNTIF(" & range_FolderId_fixed & "," & range_FolderId_cell & "))*1 +(SUMIF(" & range_FolderId_fixed & "," & range_FolderId_cell & "," & range_Is_IB_fixed & ")= COUNTIF(" & range_FolderId_fixed & "," & range_FolderId_cell & "))*1  "
  Worksheets("Sheet1").UsedRange.AutoFilter Field:=7, Criteria1:="=1"

结束子

Answer 1

公式解决方案，没有 VBA:
=IF(SUMPRODUCT(--($A$2:$A$7=A2),--(ISNUMBER($B$2:$B$7)))=1,"delete","keep")