title: With Excel, trying to find genuine used range from external HTA
body: <p>I've been using this command:</p>
  
  <pre><code>LastRow = ActiveSheet.UsedRange.Rows.Count
  </code></pre>
  
  <p>But the UsedRange property can often be inaccurate. So I'm looking for an alternative. I found a great tip explaining this method:</p>
  
  <pre><code>LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  </code></pre>
  
  <p>This works a treat in Excel. However, I'm running code from an HTA, so I need to convert this line, and I'm struggling. THIS is my question --- please can someone offer guidance on how to convert this simple code into an HTA-compatible one?</p>
  
  <p>Out of interest, to try to circumvent this problem, I tried writing a routine in a way that I understand. The result <em>works</em>, but is very slow. Here it is for giggles:</p>
  
  <pre><code>    Set objExcel = GetObject(,"Excel.Application") 
      wb = "test.xlsx"
      objExcel.Workbooks(wb).Activate
      wbactiveSheet = objExcel.Workbooks(wb).ActiveSheet.Name
  
      'determine rows and columns in range of first xls
      theNumRow = objExcel.Workbooks(wb).Sheets(wbactiveSheet).UsedRange.Rows.Count
      theNumCol = objExcel.Workbooks(wb).Sheets(wbactiveSheet).UsedRange.Columns.Count
  
      'determine genuine used rows:
      x = 1 'start at first row
      blankRows = 0
      Do 'each row
          y = 1 'start at first column
          blankCells = 0
          Do 'each column
              If objExcel.Workbooks(wb).Sheets(wbactiveSheet).Cells(x, y).Value &lt;&gt; "" Then
                  theNumRowActual = x
                  'found non-blank, skip to next row
                  Exit Do 'the columns loop
              Else
                  blankCells = blankCells + 1
              End If
              y = y + 1
          Loop Until (y - 1) = theNumCol
          If blankCells = theNumCol Then 'blank row
              blankRows = blankRows + 1
              If blankRows = 50 Then
                  Exit Do
              End If
          Else
              blankRows = 0
          End If
          x = x + 1
      Loop Until x = theNumRow 'i.e. until the testing area matches the usedRange property
  
      'determine genuine used columns:
      y = 1 'start at first column
      blankCols = 0
      Do 'each column
          x = 1 'start at first row
          blankCells = 0
          Do 'each row
              If objExcel.Workbooks(wb).Sheets(wbactiveSheet).Cells(x, y).Value &lt;&gt; "" Then
                  theNumColActual = y
                  'found non-blank, skip to next column
                  Exit Do 'the rows loop
              Else
                  blankCells = blankCells + 1
              End If
              x = x + 1
          Loop Until (x - 1) = theNumRowActual
          If blankCells = theNumRowActual Then 'blank column
              blankCols = blankCols + 1
              If blankCols = 50 Then
                  Exit Do
              End If
          Else
              blankCols = 0
          End If
          y = y + 1
      Loop Until (y - 1) = theNumCol 'i.e. until the testing area matches the usedRange property
      'bug
  
  
      MsgBox "USEDRANGEMETHOD:" &amp;vbNewLine &amp; "rows: " &amp; theNumRow &amp; vbNewLine &amp; "columns: " &amp; theNumCol &amp; vbNewLine &amp; vbNewLine &amp; "NEWMETHOD:" &amp; vbNewLine &amp; "rows: " &amp; theNumRowActual &amp; vbNewLine &amp; "columns: " &amp; theNumColActual
  </code></pre>
  
  <p>Thank you</p>