title: Subscript out of range VBA Excel array
body: <p>I have this code to eliminate all anomalies in the denormlaized hierarchy table. When I try to run this macro with a hundred rows of records, maybe around 200 to 300, it works just fine. But when I try to run the macro with all of my rows, which is around 18,000 lines, it returns the "Subscript out of range" error. I'm not sure what's wrong with the code 'cause it seems to work fine with hundreds of rows. I'm using MS Excel 2010 version. Any help would be appreciated, thank you very much.</p>
  
  <p>Here's my working code:</p>
  
  <pre><code>    Option Explicit
  
  Sub EliminateAnomaliesDH()
  Sheets("Denorm Hier").Select
  Range("A1").Select
  Dim iCtr As Integer
  Dim arr As Variant
  
  iCtr = 2
  
  While Range("B" &amp; iCtr).Value &lt;&gt; ""
  arr = Split(Range("B" &amp; iCtr).Value, "[")
  arr = Split(arr(1), "]")
  
  Select Case arr(0)
      Case "L1"
          Range("F" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L2"
          Range("H" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L3"
          Range("J" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L4"
          Range("L" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L5"
          Range("N" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L6"
          Range("P" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L7"
          Range("R" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L8"
          Range("T" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L9"
          Range("V" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L10"
          Range("X" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L11"
          Range("Z" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
      Case "L12"
          Range("AB" &amp; iCtr &amp; ":AB" &amp; iCtr &amp; "").Value = ""
  End Select
  
  iCtr = iCtr + 1
  Wend
  
  Sheets("Instructions").Select
  MsgBox "Successfully removed all anomalies of the Denormalized hierarchy Table"
  End Sub
  </code></pre>