End_Row = Range("A" & Rows.Count).End(xlUp).Row

[Ark68]

Consider the code below:

Sub Move_Diamonds()
    
    Dim rngToCopy As Range
    Dim End_Row As Range
    Dim llastrow As Long
    Dim lcopyrows As Long
    lcopyrows = 0
    llastrow = 0
    With Workbooks("Rental_Main.xls").Worksheets("CLASS_Data")
         If .FilterMode Then .ShowAllData
         llastrow = Range("A65536").End(xlUp).Row
         MsgBox ("The Last Row Is: ") & llastrow
         With .Range("A1:J" & llastrow)
            .AdvancedFilter _
                    Action:=xlFilterInPlace, _
                    CriteriaRange:=Worksheets("Criteria").Range("D4:D5"), _
                    Unique:=False
            Set rngToCopy = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            lcopyrows = rngToCopy.Count
            MsgBox ("Diamonds Found To Relocate: ") & lcopyrows
            If lcopyrows = 0 Then
                MsgBox ("There are NO diamonds found.")
            End If
            Workbooks("SportsOps.xls").Worksheets("MAIN").Range("D22").Value = lcopyrows
  
        End With
 
        If .FilterMode Then .ShowAllData
    End With

   If Not rngToCopy Is Nothing Then rngToCopy.EntireRow.Copy Destination:=Workbooks("Rental_Main.xls").Worksheets("Diamonds_Regular").Range("A2")
   End_Row = Range("A" & Rows.Count).End(xlUp).Row
   Range("K2:K" & End_Row).Formula = "=VLOOKUP(CONCATENATE(C2,D2),FACILITIES!$A$1:$B$100,2,FALSE)"
   With Application

        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
        
End Sub

I am receiving an error (Run Time Error. Object variable or With Block variable not set) with the highlighted red line.

Where have I messed up?

Jenn

 

[Oaktree]

You've declared End_Row as a range, but are using End_Row = (number)

Try Dim End_Row As Long instead

 

[Ark68]

Oaktree, thank you. That worked.