title: Excel VBA find last entry in an array (but not in the spreadsheet)
body: <p>I have some Excel code that assembles an array of file names, and then loops through and extracts some data from them.  The data is not in the spreadsheet itself - it is completely assembled within VBA.  New files are added each month, so the number will vary.  </p>
  
  <p>My problem is that code that was working is no longer working, and I'm trying to figure out a workaround.  (Related: <a href="https://stackoverflow.com/questions/47840242/error-microsoft-excel-has-stopped-working-but-i-didnt-change-anything">Error: Microsoft Excel has stopped working - But I didn&#39;t change anything</a>)</p>
  
  <p>UBound finds the size of the array.  But the array is not completely filled with data.  How do I find the last item in the array that has something in it?</p>
  
  <p>I am searching and finding answers that relate to finding the number of items on a spreadsheet, but this doesn't really use a worksheet.  IT seems like CountA might be what I want, but <a href="https://stackoverflow.com/questions/5065432/excel-find-last-value-in-an-array">Excel: Find last value in an array</a> doesn't have an example that I can figure out to make work in my case.</p>
  
  <p>In other words, I'd like to use something besides UBound in the code below, so I don't go past the entries that have something in them.</p>
  
  <pre><code>    FName = Array("april2010.xls", "feb2010.xls", "jan2010.xls", "july2010.xls", "june2010.xls", _
              "mar2010.xls", "may2010.xls", "sep2010.xls", "..\FINAL-MO-BAL-2011\APRIL2011.xls", _
              "..\FINAL-MO-BAL-2011\AUG2011.xls", "..\FINAL-MO-BAL-2011\DEC2011.xls", _
              "..\FINAL-MO-BAL-2011\FEB2011.xls", "..\FINAL-MO-BAL-2011\JAN2011.xls", _
              "..\FINAL-MO-BAL-2011\JULY2011.xls", "..\FINAL-MO-BAL-2011\JUNE2011.xls", _
              "..\FINAL-MO-BAL-2011\MARCH2011.xls", "..\FINAL-MO-BAL-2011\MAY2011.xls", _
              "..\FINAL-MO-BAL-2011\NOV2011.xls", "..\FINAL-MO-BAL-2011\OCT2011.xls", _
              "..\FINAL-MO-BAL-2011\SEP2011.xls", FName2, FName3, FName4, FName5, FName6, _
              FName7, FName8, FName9, FName10, FName11, FName12, FName13, FName14, FName15, _
              FName16, FName17, FName18, FName19, FName20, FName21, FName22, FName23, FName24, _
              FName25, FName26, FName27, FName28, FName29, FName30, FName31, FName32, FName33, _
              FName34, FName35, FName36, FName37, FName38, , FName39, FName40, FName41, FName42, _
              FName43, FName44, FName45, FName46, FName47, FName48, FName49)
  
  If IsArray(FName) Then
      Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
      WorkbookName = ThisWorkbook.Name
      rnum = 1
      For Fnum = LBound(FName) To UBound(FName)
          Set mybook = Nothing
          On Error Resume Next
          Set mybook = Workbooks.Open(Filename:=FName(Fnum), ReadOnly:=True)
  
          With Application
              CalcMode = .Calculation
              .Calculation = xlCalculationManual
              .ScreenUpdating = False
              .EnableEvents = False
              .CutCopyMode = False
              .DisplayAlerts = False
              .Visible = False
          End With
  
          On Error GoTo 0
  
          If Not mybook Is Nothing Then
              On Error Resume Next
  </code></pre>