Hi JFKjr. Not sure why this fails on second and subsequent passes. I suspect that the use of [A1] as a cell reference is part of the issue. However, there is no need to do a search at all to find the last row (and the last column).
The following two functions - part of an automated Excel query transfer module I developed - return the last row and column of object objExcel (which is global in scope within the class module concerned).
- Function LastRow(Optional worksheetname As String = "") As Long
-
' Returns the last row number in the active sheet,
-
' or the specified worksheet if a worksheet name is provided
-
Dim rngLast As Range
-
If worksheetname = "" Then
-
Set rngLast = objExcel.ActiveSheet.Range("A1").SpecialCells(xlLastCell)
-
Else
-
Set rngLast = objExcel.Worksheets(worksheetname).Range("A1").SpecialCells(xlLastCell)
-
End If
-
LastRow = rngLast.Row
-
End Function
-
-
Function LastColumn(Optional worksheetname As String = "") As Long
-
' Returns the last row number in the active sheet,
-
' or the specified worksheet if a worksheet name is provided
-
Dim rngLast As Range
-
If worksheetname = "" Then
-
Set rngLast = objExcel.ActiveSheet.Range("A1").SpecialCells(xlLastCell)
-
Else
-
Set rngLast = objExcel.Worksheets(worksheetname).Range("A1").SpecialCells(xlLastCell)
-
End If
-
LastColumn = rngLast.Column
-
End Function
Feel free to extract/adapt the code as necessary.
-Stewart