Last row used in a closed workbook sheet

[peterv6]

I know there's a way to find the last row used in a range in a sheet, but I need to know if there is a way (using vba) to determine what the last row used in a range is in a sheet in a closed workbook.

 

[Andrew Poulsom]

Is the data in the closed workbook contiguous? Is so it will be possible to use the COUNTA worksheet function to determine the last row.

 

[peterv6]

Yes the data is contiguous. Can you help me with the syntax?

 

[Andrew Poulsom]

Try something like this:

Sub Test()
'   file name, sheet name and column number - change to suit
    Const FName As String = "P:\TEMP\MrExcel\FileLinks\[MyFile.xls]"
    Const ShName As String = "Sheet1"
    Const ColNo As Integer = 1
    Dim ShNew As Worksheet
    Dim LastRow As Long
    Application.DisplayAlerts = False
    Set ShNew = Worksheets.Add
    With ShNew.Range("A1")
        .FormulaR1C1 = "=COUNTA('" & FName & ShName & "'!C" & ColNo & ")"
        LastRow = .Value
    End With
    ShNew.Delete
    Application.DisplayAlerts = True
    MsgBox LastRow
End Sub

 

[peterv6]

Andrew, Outstanding! Thank you very much. This does just what I need. I do have one question, however. When I run this subroutine, I get a pop-up box titled "Select Sheet", and I have to manually select the sheet I want to get the information from. Can you tell me what I might be doing incorrectly, and what might fix this?

Sub Test()
' file name, sheet name and column number - change to suit
Const FName As String = "C:\Assignments\Metrics Assignments\Test Output\August\[Opened in August.xls]Subtotals"
Const ShName As String = "Subtotals"
Const ColNo As Integer = 1
Dim ShNew As Worksheet
Dim LastRow As Long
Application.DisplayAlerts = False
Set ShNew = Worksheets.Add
With ShNew.Range("A1")
.FormulaR1C1 = "=COUNTA('" & FName & ShName & "'!C" & ColNo & ")"
LastRow = .Value
End With
ShNew.Delete
Application.DisplayAlerts = True
MsgBox LastRow
End Sub

Thanks again!!!!

 

[Andrew Poulsom]

I guess it's because you have the sheet name (Subtotals) at the end of the FName string. Try removing it.

 

[peterv6]

Andrew, my apologies! After posting, and seeing my subroutine below yours, I saw the problem. I added the sheet name to the end of the file. When I took the filename off, running it didn't produce the select sheets pop-up box, but it returned a value of 1, which was incorrect. (It should have been 9). What I did to fix it, was add the sheet name to the filename, and remove the sheet name from the formula line. (see below).
Thanks again, as I would never have figured this out without your help!!!!

Sub Test()
' file name, sheet name and column number - change to suit
Const FName As String = "C:\Assignments\Metrics Assignments\Test Output\August\[Opened in August.xls]Subtotals"
' Const FName As String = "C:\Assignments\Metrics Assignments\Test Output\August\[Opened in August.xls]"
Const ShName As String = "Subtotals"
Const ColNo As Integer = 1
Dim ShNew As Worksheet
Dim LastRow As Long
Application.DisplayAlerts = False
Set ShNew = Worksheets.Add
With ShNew.Range("A1")
.FormulaR1C1 = "=COUNTA('" & FName & "'!C" & ColNo & ")"
' .FormulaR1C1 = "=COUNTA('" & FName & ShName & "'!C" & ColNo & ")" <-- Returned 1 (incorrect)
LastRow = .Value
End With
ShNew.Delete
Application.DisplayAlerts = True
MsgBox LastRow
End Sub

 

[TDCox]

Thank you for this thread, which has helped with a particular issue today - I've updated the code as below

Private Function LastRowFromClosedFile(InFolder As String, InFile As String, OnSheet As String, Optional FromCell As String = "A1", Optional InColumn As Long = 1) As Long
'
' LastRowFromClosedFile Function
'
' Based on a macro created 2005-08-08 by Andrew Poulsom (https://www.mrexcel.com/forum/members/andrew-poulsom.html) shared on https://www.mrexcel.com/forum/excel-questions/155993-last-row-used-closed-workbook-sheet.html
' Created 2018-11-22 by Timothy Daniel Cox
' last edited 2018-11-22 by Timothy Daniel Cox
'
' Returns the LastRow on a given sheet from a closed file: returns "-1" if there was an error
'
' Uses: N/A
' References: N/A
'
On Error GoTo ErrorHandler
    Dim ShNew As Worksheet
    Application.DisplayAlerts = False
    Set ShNew = Worksheets.Add
    With ShNew.Range("A1")
        .FormulaR1C1 = "=COUNTA('" & InFolder & "[" & InFile & "]" & ECMSDataSheetName & "'!C" & InColumn & ")"
        LastRowFromClosedFile = .Value
    End With
    ShNew.Delete
    Application.DisplayAlerts = True
Exit Function
ErrorHandler:
    Err.Clear
    LastRowFromClosedFile = -1
End Function

Try something like this:

Sub Test()
'   file name, sheet name and column number - change to suit
    Const FName As String = "P:\TEMP\MrExcel\FileLinks\[MyFile.xls]"
    Const ShName As String = "Sheet1"
    Const ColNo As Integer = 1
    Dim ShNew As Worksheet
    Dim LastRow As Long
    Application.DisplayAlerts = False
    Set ShNew = Worksheets.Add
    With ShNew.Range("A1")
        .FormulaR1C1 = "=COUNTA('" & FName & ShName & "'!C" & ColNo & ")"
        LastRow = .Value
    End With
    ShNew.Delete
    Application.DisplayAlerts = True
    MsgBox LastRow
End Sub

 

[TDCox]

...though "ECMSDataSheetName" should of course be "OnSheet" (can't edit)