title: Excel VBA: Generating a new array from an existing array, but skipping specific strings
body: <p>Here is what I am trying to do in Excel.</p>
  
  <p>Simply put, I am trying to take a 2D array, (1) convert it into a 1D array, (2) cycle through the 1D array, (3) copy any values that aren't specific strings to a new array, and (4) then write that new, trimmed 1D array to a specific column.</p>
  
  <p>More complexly put, I am trying to take two 2D arrays, convert them both into matched 1D arrays, cycle through them both, but only copy the contents based on one of the arrays into two different arrays, and then write the new arrays into two different columns (didn't explain that all that well...)</p>
  
  <p>With my rudimentray VBA knowledge, cobbled together from what I can find online, I have somehow managed to write some code that accomplishes (1), (2), and (4). The issues I am having is with (3). I can not seem to get it to skip over the specific cells.</p>
  
  <p>Does anyone have any advice for how this can be done?</p>
  
  <p>Below is the code I cobbled together. Be forewarned, this is the first code I have written, so I am guessing that there are far simpler and more elegant ways to do this; I did what worked for me. Any advice on tweaks would be greatly appreciated!</p>
  
  <pre><code>Sub Calculating()
  
  'Transforming 2D Arrays into 1D Arrays
  
  'Defining the arrays
  Dim InputNameArray() As Variant 'Input Names (strings)
  Dim InputValueArray() As Variant 'Input Values (numbers)
  Dim InputArrayR As Long 'Old Array Row
  Dim InputArrayC As Long 'Old Array Column
  
  Dim OldArrayP As Long 'Old Array Position
  
  Dim OldNameArray() As Variant 'One Dimensional Names
  Dim OldValueArray() As Variant 'One Dimensional Values
  
  InputNameArray = Range("B3:M10")
  InputValueArray = Range("B27:M34")
  
  OldArrayP = 0 'Old Array One Dimensional Position
  
  For InputArrayR = 1 To UBound(InputNameArray, 1)
      For InputArrayC = 1 To UBound(InputNameArray, 2)
  
          ReDim Preserve OldNameArray(0 To OldArrayP)
          OldNameArray(OldArrayP) = InputNameArray(InputArrayR, InputArrayC)
  
          ReDim Preserve OldValueArray(0 To OldArrayP)
          OldValueArray(OldArrayP) = InputValueArray(InputArrayR, InputArrayC)
  
          Debug.Print OldArrayP; OldNameArray(OldArrayP), OldValueArray(OldArrayP)
  
          OldArrayP = OldArrayP + 1
  
      Next InputArrayC
  Next InputArrayR
  
  
  
  'Scanning through 1D Arrays to Eliminate Specific Values
  
  'Defining New Arrays
  
  Dim NewNameArray() As Variant 'New Name Array (Strings)
  Dim NewValueArray() As Variant 'New Value Array (Numbers)
  
  Dim NewArrayP As Long 'New Array Position
  Dim OldArrayPosition As Long 'Old Array Position
  
  NewArrayP = 0
  
  For OldArrayPosition = LBound(OldNameArray) To UBound(OldNameArray)
      If OldNameArray(OldArrayPosition) &lt;&gt; "Blank" Or OldNameArray(OldArrayPosition) &lt;&gt; "Standard-100" Or OldNameArray(OldArrayPosition) &lt;&gt; "Standard-50" Or OldNameArray(OldArrayPosition) &lt;&gt; "Standard-25" Or OldNameArray(OldArrayPosition) &lt;&gt; "Standard-12.5" Or OldNameArray(OldArrayPosition) &lt;&gt; "Standard-6.25" Or OldNameArray(OldArrayPosition) &lt;&gt; "Standard-3.125" Or OldNameArray(OldArrayPosition) &lt;&gt; "Standard-1.5625" Or OldNameArray(OldArrayPosition) &lt;&gt; "Standard-0.7825" Then
          ReDim Preserve NewNameArray(0 To NewArrayP)
              NewNameArray(NewArrayP) = OldNameArray(OldArrayPosition)
          ReDim Preserve NewValueArray(0 To NewArrayP)
              NewValueArray(NewArrayP) = OldValueArray(OldArrayPosition)
  
          Debug.Print OldArrayPosition, OldNameArray(OldArrayPosition), OldValueArray(OldArrayPosition)
          Debug.Print NewArrayP, NewNameArray(NewArrayP), NewValueArray(NewArrayP)
  
          NewArrayP = NewArrayP + 1
      End If
  Next OldArrayPosition
  
  'Outputing Values
  
  'Defining Variables
  
  Dim OutputPosition As Long 'Output Array Position
  Dim OutputRow As Long 'Output Row
  
  OutputRow = 3
  
  For OutputPosition = LBound(NewNameArray) To UBound(NewNameArray)
      Cells(OutputRow, "O").Value = NewNameArray(OutputPosition)
      Cells(OutputRow, "Q").Value = NewValueArray(OutputPosition)
  
      Debug.Print OutputRow, OutputPosition, NewNameArray(OutputPosition), NewValueArray(OutputPosition)
  
      OutputRow = OutputRow + 1
  Next OutputPosition
  
  
  'Cleaning Up
  
  Erase InputNameArray
  Erase InputValueArray
  Erase OldNameArray
  Erase OldValueArray
  Erase NewNameArray
  Erase NewValueArray
  
  End Sub
  </code></pre>