Error 1004 - Method Range of Object Worksheet failed

[Razor_Rob]

Im getting the Error 1004 Method Range of Object Worksheet failed, not sure what Im missing here...

Option Explicit

Sub CleanUp()

Dim wbImport As Workbook
Dim wsImport As Worksheet
Dim cell As Range
Dim fileNameAndPath As Variant
Dim lDestLastRow As Long

    'Open a workbook
    fileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), *.csv", Title:="Select File To Be Opened")
    If fileNameAndPath = False Then Exit Sub
    'Workbooks.Open Filename:=fileNameAndPath
    Set wbImport = Workbooks.Open(Filename:=fileNameAndPath, ReadOnly:=True)
    Set wsImport = wbImport.Worksheets(1)
    
Application.EnableEvents = False

    'Add Zero in front of the mobile number
    With wsImport
        .Range("Q3:Q" & lDestLastRow).NumberFormat = "@"            'format range as text
       
        For Each cell In .Range("Q3:Q" & lDestLastRow)
             cell.Value = Format(cell * 1, "0000000000")      'Convert each cell
        Next cell
    End With
    
    'Remove spaces from the mobile number
    With wsImport
        .Range("Q3:Q" & lDestLastRow).Replace " ", vbNullString, xlPart
    End With
    
    'Clear contents of existing data range
    wsImport.Range("AO3:AY,BB3:BF " & lDestLastRow).ClearContents
    
    'Close the Source Workbook
    wbImport.Close SaveChanges:=False
    
    MsgBox "Added Zeros in front of the Mobile Number and Deleted Employer Info not required", vbOKOnly
    
    Application.EnableEvents = True
    
End Sub

 

[dave3009]

When does lDestLastRow get set?

 

[Razor_Rob]

Yeah just realised it, all sorted
Thanks