Last Row of Data in Column O

[mrsushi]

Good morning,

Can you help and advise how the range can be tweaked so the results in column is cleaned up ie make this more dynamic as the number of items in column O can change ?

Column O has 1446 rows, but the code products an output to row 1472. Is it possible to apply something like Lastrow= Range("O" & Rows.Count).End(xlUp).Row? if so, how can the below be amended to make this work?

Many thanks
M


Sub vba_check_workbook()

Dim myFolder As String
Dim myFileName As String
Dim myRange As Range
Dim myCell As Range

Application.ScreenUpdating = False

Set myRange = Range("O2:O1472")
myFolder = "S:\Other\invoices"

For Each myCell In myRange
myFileName = myCell.Value
If Dir(myFolder & "\" & myFileName) = "" Then
myCell.Offset(0, 1) = "File Doesn't Exists."
Else
myCell.Offset(0, 1) = "File Exists"
End If
Next myCell

Application.ScreenUpdating = True

End Sub

 

[Legacy 143009]

Like this?

Sub vba_check_workbook()

Dim myFolder As String
Dim myFileName As String
Dim myRange As Range
Dim myCell As Range
Dim Lastrow As Long
Lastrow= Range("O" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False

Set myRange = Range("O2:O" & Lastrow)
myFolder = "S:\Other\invoices"

For Each myCell In myRange
myFileName = myCell.Value
If Dir(myFolder & "\" & myFileName) = "" Then
myCell.Offset(0, 1) = "File Doesn't Exists."
Else
myCell.Offset(0, 1) = "File Exists"
End If
Next myCell

Application.ScreenUpdating = True

End Sub

 

[mrsushi]

Like this?

Sub vba_check_workbook()

Dim myFolder As String
Dim myFileName As String
Dim myRange As Range
Dim myCell As Range
Dim Lastrow As Long
Lastrow= Range("O" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False

Set myRange = Range("O2:O" & Lastrow)
myFolder = "S:\Other\invoices"

For Each myCell In myRange
myFileName = myCell.Value
If Dir(myFolder & "\" & myFileName) = "" Then
myCell.Offset(0, 1) = "File Doesn't Exists."
Else
myCell.Offset(0, 1) = "File Exists"
End If
Next myCell

Application.ScreenUpdating = True

End Sub

Perfect. Many thanks FlashBond.

Regards
M

 

[Legacy 143009]

Glad it did work! Thanks for the feedback