VB Code to find the 1st blank row in closed workbook

Try this code

Sub Test()
    Dim strPath     As String
    Dim wb          As Workbook
    Dim ws          As Worksheet
    Dim lr          As Long

    Application.ScreenUpdating = False
        strPath = ThisWorkbook.Path & "\Book1.xlsb"
        Set wb = Workbooks.Open(strPath)
        Set ws = wb.Worksheets("Data")
        lr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
        MsgBox lr
        '>~~ Your Code
    
        wb.Close False
    Application.ScreenUpdating = True
End Sub

This can also be done with a simple formula

Formula:

=MATCH(TRUE,INDEX(A:A="",,),)

I am very sorry Riz. I should've read carefully the description about closed workbook. You would need VBA for this.

You're welcome. Glad I can offer some help