title: Excel VBA deleting rows that have mixed values for a given index
body: <p>I have the following data</p>
  
  <pre><code>Name     ID    Value
  
  Alice    12C    500
  
  Bob      14     60
  
  Dan       15C    64
  
  Dan       1C    25
  
  Alice    4       556
  
  Bob      11     455
  </code></pre>
  
  <p>In my data, Alice has both numerical (4) and string+numerical ID (12C) and I want to delete all Alice rows, while I want to hold on to data of names where their ID is strictly numeric (Bob 11, 14) or strictly string+numeric (Dan 15C , 1C).</p>
  
  <p>First I make an array of unique Name entries:</p>
  
  <pre><code>   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 &lt;&gt; 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
  </code></pre>
  
  <p>Then I make an Array of the row numbers that contain a particular Name</p>
  
  <pre><code>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 &amp; ":" &amp; Cells(FinalRow,  FolderId_column).Address
  
     For Each itm5 In FID_Array()
  
             Count_FID_Array(n) = Application.CountIf(" &amp; range_FID &amp; ", " &amp; itm5 &amp; ")
             ReDim Preserve Count_FID_Array(1 To n + 1)
             n = n + 1
  
      Next itm5
  </code></pre>
  
  <p>I don't think my CountIf is working. I have tried to store the value of Count_FID_Array in another cell in a different sheet but I am getting #value!</p>
  
  <p>If I got the countIf to work then I was going to sort the data by name, then double loop to check the ID variable the next "n" times to see if the last digit was "C" for all of them or to check if the ID was numeric for all of them.</p>
  
  <p>Can you please point out why my countif is not working and is there a smarter way to do this? </p>
  
  <p>I am using arrays of names here because in the end I want to feed the array into an  autofilter and delete the rows that I don't want.</p>
  
  <p><strong>Update 1 3:45 PM Nov 21 2013: I have solved this as following:</strong></p>
  
  <p>I basically created three columns. First column was 0 or 1 depending on if the the ID was all numbers. The second column was 0 or 1 depending on if the last digit was "C" (in my real work the last two digits are "IB" ) and finally I compared the frequency of these occurences to the frequency of the Name itself. If any of those match then I give it the number 1 else 0. I use this index later to autofilter.</p>
  
  <p>Now I'll try to use zx8754's shorter formula in the VBA code and I will try to address the issues regarding Countif that Joe has raised.</p>
  
  <p>Sub conditionsforsubfolders()</p>
  
  <pre><code>  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 &amp; ":" &amp; Cells(FinalRow, FolderId_column).Address
     range_TradeId_fixed = Cells(2, TradeId_column).Address &amp; ":" &amp; Cells(FinalRow, TradeId_column).Address
     range_Isnumber = Cells(2, Isnumber_Column).Address &amp; ":" &amp; Cells(FinalRow, Isnumber_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
     range_Isnumber_fixed = Cells(2, Isnumber_Column).Address &amp; ":" &amp; Cells(FinalRow, Isnumber_Column).Address
     range_Is_IB = Cells(2, Is_IB_Column).Address &amp; ":" &amp; Cells(FinalRow, Is_IB_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
     range_Is_IB_fixed = Cells(2, Is_IB_Column).Address &amp; ":" &amp; 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 &amp; ":" &amp; Cells(FinalRow, Exceptions_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
  
  
     Range(range_Isnumber).Formula = "=Isnumber(" &amp; range_TradeId_cell &amp; ")*1"
     Range(range_Is_IB).Formula = "=(RIGHT(" &amp; range_TradeId_cell &amp; ",2)= ""IB"")*1"
  
     Range(range_Exceptions).Formula = "=(SUMIF(" &amp; range_FolderId_fixed &amp; "," &amp; range_FolderId_cell &amp; "," &amp; range_Isnumber_fixed &amp; ")= COUNTIF(" &amp; range_FolderId_fixed &amp; "," &amp; range_FolderId_cell &amp; "))*1 +(SUMIF(" &amp; range_FolderId_fixed &amp; "," &amp; range_FolderId_cell &amp; "," &amp; range_Is_IB_fixed &amp; ")= COUNTIF(" &amp; range_FolderId_fixed &amp; "," &amp; range_FolderId_cell &amp; "))*1  "
    Worksheets("Sheet1").UsedRange.AutoFilter Field:=7, Criteria1:="=1"
  </code></pre>
  
  <p>End Sub</p>