vba - With Excel, trying to find genuine used range from external HTA -

i've been using command:

lastrow = activesheet.usedrange.rows.count 

but usedrange property can inaccurate. i'm looking alternative. found great tip explaining method:

lastrow = cells.find("*", searchorder:=xlbyrows, searchdirection:=xlprevious).row 

this works treat in excel. however, i'm running code hta, need convert line, , i'm struggling. question --- please can offer guidance on how convert simple code hta-compatible one?

out of interest, try circumvent problem, tried writing routine in way understand. result works, slow. here giggles:

    set objexcel = getobject(,"excel.application")      wb = "test.xlsx"     objexcel.workbooks(wb).activate     wbactivesheet = objexcel.workbooks(wb).activesheet.name      'determine rows , 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 @ first row     blankrows = 0     'each row         y = 1 'start @ first column         blankcells = 0         'each column             if objexcel.workbooks(wb).sheets(wbactivesheet).cells(x, y).value <> ""                 thenumrowactual = x                 'found non-blank, skip next row                 exit 'the columns loop             else                 blankcells = blankcells + 1             end if             y = y + 1         loop until (y - 1) = thenumcol         if blankcells = thenumcol 'blank row             blankrows = blankrows + 1             if blankrows = 50                 exit             end if         else             blankrows = 0         end if         x = x + 1     loop until x = thenumrow 'i.e. until testing area matches usedrange property      'determine genuine used columns:     y = 1 'start @ first column     blankcols = 0     'each column         x = 1 'start @ first row         blankcells = 0         'each row             if objexcel.workbooks(wb).sheets(wbactivesheet).cells(x, y).value <> ""                 thenumcolactual = y                 'found non-blank, skip next column                 exit 'the rows loop             else                 blankcells = blankcells + 1             end if             x = x + 1         loop until (x - 1) = thenumrowactual         if blankcells = thenumrowactual 'blank column             blankcols = blankcols + 1             if blankcols = 50                 exit             end if         else             blankcols = 0         end if         y = y + 1     loop until (y - 1) = thenumcol 'i.e. until testing area matches usedrange property     'bug       msgbox "usedrangemethod:" &vbnewline & "rows: " & thenumrow & vbnewline & "columns: " & thenumcol & vbnewline & vbnewline & "newmethod:" & vbnewline & "rows: " & thenumrowactual & vbnewline & "columns: " & thenumcolactual 

thank you

it looks have worksheet open , active? in case:

const xlbyrows = 1 const xlprevious = 2  set objexcel = getobject(,"excel.application") lastrow = objexcel.activesheet.cells.find("*", , , , xlbyrows, xlprevious).row 

see recent post using vbscript call excel function additional tips.

when you're programming using vba within excel cells native object can reference directly. when you're outside of excel -- in hta or wsh script -- script has no idea cells refers to. excel objects have ones create. must work way down chain, main excel (application) object workbook , sheet before can operate on cells property. might make more sense if use same names excel does:

' define same objects excel does... set application = getobject(, "excel.application") set thisworkbook = application.activeworkbook  ' sheet based on name ("sheet1", in example).. set mysheet = thisworkbook.sheets("sheet1")  ' , operate on cells collection... mysheet.cells.find(...) 

as shortcut, excel provides activesheet property can use directly on application object current sheet, bypassing workbook layer. that's i've used in first code snippet.