VB Script function to get last utilized row in an Excel ss

[montanan]

I'm wondering if any of the experienced VB Scripters out there have defined a function to get the last utilized row in an Excel spreadsheet.

I'm making great use of the following code example Marcus provided...

Code: Select all

VBSTART
Dim xlApp
Dim xlBook

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename)
end sub

'Use this to close Excel later
Sub CloseExcel
   xlApp.quit
   Set xlApp = Nothing
End Sub

'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  GetCell = xlSheet.Cells(Row, Column).Value
End Function

'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.Cells(Row,Column).Value = NewValue
End Function

VBEND

.... and then I tried, though unsuccessfully, adding the following after finding a reference for such a function on the web (http://www.mrexcel.com/td0058.html) ...

Code: Select all

'Gets last active row of specified worksheet
Function LastRow(Sheet,Column)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  LastRow = xlSheet.LastRow.UsedRange.Rows.Count
End Function

I don't really know VB Script, so I hope that one of the forum members will see this and help me to develop a working function.

This will be a very useful function for a lot of list management or list-based processing routines reliant upon Excel.

[montanan]

I came up with a subroutine that works well and executes quickly...

Code: Select all

SRT>CalcLastRow
 Message>Calculating list size
 Let>z=1
 Label>getlastrow
 VBEval>GetCell("List",%z%,6),rowcheck
 Length>%rowcheck%,lastrowlen
 If>lastrowlen=0
  Let>z=z+1
  Goto>endcalc
 EndIf
 Let>z=z+1
 Goto>getlastrow
 Label>endcalc
 Message>There are %z% rows
End>CalcLastRow

... but would still like to figure out the VB Script function.

[gdyvig]

I think you need to pass in the book as an argument to your function. At least I do in my script. Also, I don't use "LastRow.UsedRange". And I use "ActiveWorkbook"

Here is what my code looks like:

set xlSheet=xlApp.ActiveWorkbook.Worksheets(Sheet)
LastRow=xlSheet.UsedRange.Rows.Count

I found the last row in the same function that opened the spreadsheet, so you may need to do it a little differently in a separate function.

Hope this helps,

Gale

[montanan]

Thanks, Gale.

I appreciate the direction on this one.