Find bottom of a column in Excel using VBScript

Question

I'm writing a script that will extract the contents of a column in an Excel spreadsheet. Since I don't know how many rows will have data, I need to be able to find the end of the column in the course of the script.

In VBA, I would use something like

sht.Range(sht.Cells(row, col), sht.Cells(row, col)).End(xlDown).row

When I tried using that line in the VBScript file (I knew it probably wouldn't work, but I was hoping for some good luck), a message came back saying that 'xlDown' is undefined. I could easily create an Excel macro to do this, but I want the program to run without having to alter the original spreadsheet.

Here's part of my current script where I define the Excel objects

Set fso = CreateObject("Scripting.FileSystemObject")
set xlapp = CreateObject("Excel.Application")
Set wkbk = xlapp.Workbooks.Open(path)
'xlapp.Visible = False
Set sht = wkbk.Sheets(sheetname)
Set myfile = fso.OpenTextFile(txtfile, 2, True)
lrow = sht.Range(sht.Cells(row, col), sht.Cells(row, col)).End(xlDown).row

path, sheetname, and txtfile are previously defined strings, and row and col are previously defined integers.

Answer 1

xlDown is actually a constant. It's value is -4121 as mentioned HERE. So try something like,

const xlDown = -4121
lrow  = sht.Range(sht.Cells(row, col), sht.Cells(row, col)).End(xlDown).row

OR you can make use of xlUp also in the following code to get the rowcount of a column say Column A:

const xlUp = -4162
lrow = sht.Range("A" & sht.Rows.Count).End(xlUp).Row

Answer 2

Glad that it worked. Here is simple one which will give you the count of rows in the used range for sheet sht

lrow = sht.UsedRange.Rows.Count