How to get cell address of the last row in range

Question

Sub ToolDataExtract()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'Retrieves Target Folder From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension
  myExtension = "*.csv*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile, UpdateLinks:=False)

'Ensure Workbook has opened before moving on to next line of code
      DoEvents

'Data Extracted from Files
'Find Last Row on Excel Data

s = Range("AE2")             'THIS RANGE WILL CHANGE

Dim cellAddress As String
cellAddress = Sheet1.Cells(Sheet1.Rows.Count, 8).End(xlUp).Address

f1a = Range("R2")
f1b = Range("N2")
f1c = Range("O2")
f1d = Range("Q2")
f1e = Range("S2")
f1f = Range("P2")
f1g = Range("H2")
f1h = Range("" & cellAddress & "")


'Data Pasted into Excel File
Workbooks("file2").Worksheets("WMI LOG").Activate
Range("A1:H1") = Array("T", "H", "I", "P", "W", "O", "X1", "X2")
Cells(s + 1, 1) = f1a
Cells(s + 1, 2) = f1b
Cells(s + 1, 3) = f1c
Cells(s + 1, 4) = f1d
Cells(s + 1, 5) = f1e
Cells(s + 1, 6) = f1f
Cells(s + 1, 7) = f1g
Cells(s + 1, 8) = f1h

'Ensure Workbook has closed before moving on to next line of code
      DoEvents

 wb.Close SaveChanges:=False

'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete"

'Reset Macro Settings
ResetSettings:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True


End Sub

Answer 1

Skip getting the row number, just:

Sub simple()
    MsgBox Sheet1.Cells(Rows.Count, 8).End(xlUp).Address(0, 0)
End Sub